I have grouped the max(PhaseCode), but I would like to extract the phase description of the phaseCode as well, but it is impossible to use the Group concept for text field, How can I extract my highlighted Phase Description out and mapped with my MaxPhaseCode ? Thanks!
You can create a block which can be used to get the description based on the phase code and then be used in your table or wherever
Or
You can do your own SQL without using aggregate, but would be more complex maybe and you would lose some of the capacities from using aggregate.
May I know where can I create the block in order to get the description, would you mind to capture screen for me? Thanks.
Like this but you need to adapt for your use case. Here I am using a table, but you can use this logic anywhere.
Can you share your oml for my study? Thanks.
Check my answer above, I have put the OML. Again, this is an example and you can do a lot with this approach. Let us know if that helped you!
An alternative could be to create a SQL and use a subquery to find the max, joining on the phase code. Something like (simplified):
SELECT {Phase}.[Code], {PhaseCode}.[Description] FROM {Phase} INNER JOIN {PhaseCode} ON {PhaseCode}.[Id] = {Phase}.[Code] WHERE {Phase}.[Code] = ( SELECT Max({Phase}.[Code]) FROM {Phase} )
Of course, you need to adapt that to your actual data model and query.