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

CrossTab/Transpose - Rearrange data

timewaste
8 - 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.

8 REPLIES 8
Thableaus
17 - Castor
17 - Castor

Hi @timewaste 

 

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

 

Cheers,

timewaste
8 - 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?

Thableaus
17 - Castor
17 - Castor

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

bpatel
Alteryx
Alteryx

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

timewaste
8 - 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.

timewaste
8 - 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.

Thableaus
17 - Castor
17 - Castor

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,

timewaste
8 - Asteroid

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

Labels