There are two columns with object id and state(ex. 1,2,3) in the dataset.
Same object id can have multiple states.
Objective is two pick the greatest state for a particular object id in the distinct visual recipe.
Please let me know how to address this
Thank You for your response.
The scenario is the states are the strings. for ex: a particular object has the different states (started,review in progress, completed,....) in its life cycle.
The object can have 1 state i.e. started - In this case we need to output as started in the distinct recipe for that object.
Another object can have 2 states i.e(started, review in progress) - In this case we need to output as review in progress in the distinct recipe for this object
Hope the above statements give more clarification
The alphabetic order of the current values (started, review, completed, etc) does not match the order of the lifecycle. If you want to identify the "greatest state", then the values of that column must enabling ordering.
My suggestion, in a previous Prepare recipe, add a new column with a numerical value for the state (1, started), (2, Review), etc., then in the group by just identify the Max of that column.
I hope this helps.
Yes this can be one of the solution, but when the final table is published using the distinct recipe, as there
will be another columns in the dataset.
Whatever alias we have considered i.e 1- started, 2- review in process, 3- approved, 4- rejected.
it should print the final state in the column not the alias.
How to address this.
With your current values, your requirement is impossible to meet, because the order of those strings is not the order of your lifecycle. So you need to use values that enable sorting.
Instead of the Distinct recipe, did you try to use the Group recipe?
I hope this is clear. Good luck.
I hope this is clear now. Good luck