community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

CrossTab/Transpose - Rearrange data

Asteroid

Hello all,

 

I have data that looks like this:

 

F1F2F3F4
1AFF1A
1BFF2AB
1CFF3ABC
2AFF1D
2BFF2DE
2CFF3DEF

 

Now my goal is to output the data like this:

 

F1F2FF1FF2FF3
1AA  
1B AB 
1C  ABC
2AD  
2B DE 
2C  DEF

 

But when I use a Cross tab tool with Concatenation set , the output I get is:

 

F1F2FF1FF2FF3
1AA,,,AB,,,ABC
1BD,,,DE,,,DEF

 

 How do I accomplish this? Please advise.

Alteryx Certified Partner
Alteryx Certified Partner

Hi @timewaste 

 

Group By F1 and F2 and you'll have your desired output.

 

Cheers,

Asteroid

@Thableaus Of course, I am grouping by F1 and F2, that is why if you notice my output, F1 and F2 values are not generating as comma separated values. What else can I do here?

Alteryx Certified Partner
Alteryx Certified Partner

@timewaste 

 

Correct me if I'm not following you right

You mentioned exactly this:

 

"I have data that looks like this:

 

F1F2F3F4
1AFF1A
1BFF2AB
1CFF3ABC
2AFF1D
2BFF2DE
2CFF3DEF

 

Now my goal is to output the data like this:

 

F1F2FF1FF2FF3
1AA  
1B AB 
1C  ABC
2AD  
2B DE 
2C  DEF

"

 

 

To have your desired output, you need to group by fields F1 and F2. If you use the Cross-Tab Tool, grouping by F1 and F2, even if you use Concatenate method, you'll have this final result:

 

OutputTimeWaste.PNG

 

Cheers,

Alteryx
Alteryx

@timewaste for your method of aggregating values select "First" instead of "Concatenate".

Asteroid

@Thableaus I am sorry for the confusion. Better explained below:

 

Actual Data:

 

F1F2F3F4
11131val 1
11132val 2
11133val 41
11134val 421
11231val 1
11232val 2
11233val 41
11234val 421
11131val 1
11132val 2
11133val 43
11134val 423
11231val 1
11232val 2
11233val 44
11234val 424

 

So, the output I am getting in Crosstab is (using concatenation method):

 

F1F2F3F4F5F6
111val 1,val 1val 2,val 2val 41,val 43val 421, val 423
112val 1,val 1val 2,val 2val 41, val 44val 421, val 424

 

Rather I want the output to be:

 

F1F2F3F4F5F6
111val 1val 2val 41val 421
112val 1val 2val 43val 423
111val 1val 2val 41val 421
112val 1val 2val 44val 424

 

Thanks.

Asteroid

@bpatel Sure, I tried 'First' yet it doesn't help because the idea is not to choose the first value all the time neither the last value too. Also, the moment I load all my data at once, I see a series of concatenated strings in the values grouped under 1 cell rather than separate rows.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

No problem @timewaste 

 

I think maybe what you need to define here is the sequence of your fields' arrangement.

 

So, I'd use the Tile Tool for that:

 

TileStep1.PNG

 

Then, the Cross-Tab Tool grouping by this sequence as well:

 

CTabStep2.PNG

 

See if it works well now.

 

Cheers,

Asteroid

@Thableaus Yes, this worked. Thanks a ton. It was immense help!

Labels