Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Combining data in similar rows

briangeneration
5 - Atom

Hi! I hope someone can help me simplify my data.

 

Currently, I have data that looks like this (example):

 

IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 3
1M20CollegeAB[Null]
1M20College[Null][Null]C
1M20College[Null][Null][Null]
1M20College[Null][Null][Null]
2F25High School[Null]B[Null]
2F25High SchoolD[Null]F
3M30CollegeA[Null]

[Null]

3M30College[Null]G[Null]

 

I want it to look like this:

IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 3
1M20CollegeABC
2F25High SchoolDBF
3M30CollegeAG[Null]

 

I don't how to combine or summarize the data so it combines this way. There won't always be an answer in the special answer field, so I need it to pull from the right row. There could be two rows or several. However, some fields will be the same (or should be the same everywhere) that can act as the key. ID is the primary key.

 

Thank you in advance for the help and any suggestions!

5 REPLIES 5
Garabujo7
Alteryx
Alteryx

Hi @briangeneration ,

 

Here is my solution using the almighty multi-row formula. I've used three, one for each column.

 

 

Garabujo7_0-1632259411284.png

 

Garabujo7_2-1632259499386.png

 

 

Attached the workflow I've created.

Gabriel

 

briangeneration
5 - Atom

Hi! Thank you for taking a look at helping. The answer screenshot (I got the same answer when I ran it) is not the screenshot I'm hoping to achieve. There is data in the wrong rows, and the rows aren't simplified. So neither is correct.

 

Again, example data:

 

IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 3
1M20CollegeAB[Null]
1M20College[Null][Null]C
1M20College[Null][Null][Null]
1M20College[Null][Null][Null]
2F25High School[Null]B[Null]
2F25High SchoolD[Null]F
3M30CollegeA[Null]

[Null]

3M30College[Null]G[Null]

 

And hopeful outcome:

IDGenderAgeEducationSpecial answer 1Special answer 2Special answer 3
1M20CollegeABC
2F25High SchoolDBF
3M30CollegeAG[Null]

 

As you can see, for ID 1, A is the only answer that is not null for Special answer 1. So I should get A as the answer in the final line item. For ID 3, Special answer 3 should be [Null] since there is no data for that answer in any row for ID 3.

Maskell_Rascal
13 - Pulsar

Hi @briangeneration 

 

If you can guarantee that the Special Answer fields will only ever have one input and the rest are Nulls, you can do this with a simple summarize tool like below. 

Maskell_Rascal_0-1632260778627.png

 

Grabbing the Max value for the Special Answer fields will work because a value will always be greater than a Null. 

 

If however your data is a little more complicated, we can use a Transpose/Crosstab method. 

Maskell_Rascal_1-1632260929728.png

Here is it flipping your data, sorting it, copying the values down, flipping back, and finally finding only instances of unique values within the Special Answers fields. 

 

Attached is a sample workflow with both methods for you to try. 

 

Cheers!

Phil

Garabujo7
Alteryx
Alteryx

Got it, how about now that I grouped by ID and the summarized.

 

Garabujo7_0-1632261080438.png

 

Garabujo7_1-1632261099509.png

 

 

I've updated the workflow with the solution.

Gabriel

Ben_H
11 - Bolide

Hi @briangeneration,

 

You can just transpose the data, filter out nulls and then crosstab back to your desired layout.

Ben_H_0-1632316836351.png

Regards,

 

Ben

 

Labels