Hi Team,
I have data like below.
Id | Flag_A | Flag_B | Flag_C |
1000 | Y | N | Y |
1000 | N | Y | N |
1000 | Y | Y | N |
1000 | N | N | Y |
1000 | N | N | N |
2000 | N | N | Y |
2000 | N | N | N |
2000 | N | Y | Y |
3000 | Y | N | N |
3000 | N | N | N |
My result set should be like below:
Id | Final_Flag_A | Final_Flag_B | Final_Flag_C |
1000 | Y | Y | Y |
2000 | N | N | Y |
3000 | Y | N | N |
Its basically the logic is for a given Id, if any of the values in 'Flag_A' has 'Y' then the Final_Flag_A should be 'Y' only. This implies to all the remaining 2 fields 'Flag_B' and 'Flag_C' as well.
Any help is much appreciated.
Thanks & Regards
Dilver
Solved! Go to Solution.
How does this look, @Dilver_Shaik? The only thing that doesn't line up is the Final_flag_B for ID = 2000, but your desired output doesn't match the logic you outlined so I think the workflow is correct? Basically, the max function will find the later letter in the alphabet and as the Summarize is grouping by ID, if there's a Y anywhere this will come out as the Max, and if not it'll be an N:
@DataNath - Sorry, it was my mistake with respect to the id 2000. But your solution is correct and accepted. Having said that, Is there any other way to solve this apart from using MAX function? Just curious bcz what if my flag values are not just Y and N and something like other letters K, J, P ... with multiple values and I have to choose 1 among them.
How does this look? If you have multiple different flags, I'm not sure which you'd like to fill it with by default if your chosen letter isn't there. In this workflow, all the values for each Flag of every ID are concatenated and checked for the presence of your target letter. The chosen letter I used for the example is 'K' and so if it's present in the grouped flag, that field will just show as K. If it's not present, by default it will just show the letter that was in the last entry for that ID: