Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Converting multiple rows into single row

Dilver_Shaik
7 - Meteor

Hi Team,

 

I have data like below.

 

IdFlag_AFlag_BFlag_C
1000YNY
1000NYN
1000YYN
1000NNY
1000NNN
2000NNY
2000NNN
2000NYY
3000YNN
3000NNN

 

My result set should be like below:

 

IdFinal_Flag_AFinal_Flag_BFinal_Flag_C
1000YYY
2000NNY
3000YNN

 

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

 

4 REPLIES 4
DataNath
16 - Nebula

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_0-1660153534257.png

binuacs
17 - Castor

@Dilver_Shaik One way of doing this

 

binuacs_0-1660154196550.png

 

Dilver_Shaik
7 - Meteor

@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.

DataNath
16 - Nebula

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:

 

DataNath_0-1660160388238.png

DataNath_1-1660160406573.png

Labels