CrossTab/Transpose - Rearrange data

[timewaste]

Hello all,

I have data that looks like this:

F1	F2	F3	F4
1	A	FF1	A
1	B	FF2	AB
1	C	FF3	ABC
2	A	FF1	D
2	B	FF2	DE
2	C	FF3	DEF

Now my goal is to output the data like this:

F1	F2	FF1	FF2	FF3
1	A	A
1	B		AB
1	C			ABC
2	A	D
2	B		DE
2	C			DEF

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

F1	F2	FF1	FF2	FF3
1	A	A,,	,AB,	,,ABC
1	B	D,,	,DE,	,,DEF

 How do I accomplish this? Please advise.

[Thableaus]

Hi @timewaste 

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

Cheers,

[timewaste]

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

@timewaste 

Correct me if I'm not following you right

You mentioned exactly this:

"I have data that looks like this:

F1	F2	F3	F4
1	A	FF1	A
1	B	FF2	AB
1	C	FF3	ABC
2	A	FF1	D
2	B	FF2	DE
2	C	FF3	DEF

Now my goal is to output the data like this:

F1	F2	FF1	FF2	FF3
1	A	A
1	B		AB
1	C			ABC
2	A	D
2	B		DE
2	C			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:

Cheers,

[bpatel]

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

[timewaste]

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

Actual Data:

F1	F2	F3	F4
1	11	31	val 1
1	11	32	val 2
1	11	33	val 41
1	11	34	val 421
1	12	31	val 1
1	12	32	val 2
1	12	33	val 41
1	12	34	val 421
1	11	31	val 1
1	11	32	val 2
1	11	33	val 43
1	11	34	val 423
1	12	31	val 1
1	12	32	val 2
1	12	33	val 44
1	12	34	val 424

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

F1	F2	F3	F4	F5	F6
1	11	val 1,val 1	val 2,val 2	val 41,val 43	val 421, val 423
1	12	val 1,val 1	val 2,val 2	val 41, val 44	val 421, val 424

Rather I want the output to be:

F1	F2	F3	F4	F5	F6
1	11	val 1	val 2	val 41	val 421
1	12	val 1	val 2	val 43	val 423
1	11	val 1	val 2	val 41	val 421
1	12	val 1	val 2	val 44	val 424

Thanks.

[timewaste]

@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 · Accepted solution]

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:

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

See if it works well now.

Cheers,

[timewaste]

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