Concat() in PIVOT table based on a Sort weight

[manideep78]

Dear Community,

 I have a requirement like below in which need to implement the concat() function based on a sort weight.

Input:

Col1	Col2	Col3	Col4
A	1	Australia	CC
A	1	India	AA
A	1	US	BB
B	3	Brazil	GG
B	3	India	DD
B	3	Japan	FF

Col1, Col2  are dimensions and Col3 and Col4 are expressions. Need to Concatenate Col4 based on the order of Col3.
Expected output:

Col1	Col2	Col3	Col4
A	1	Australia, India, US	CC, AA, BB
B	3	Brazil, India, Japan	GG, DD, FF

Note: Sort is not based on any dimensions. 

Thanks in advance.

[QFabian]

Hi @manideep78  try this :

Data:
LOAD * INLINE [
F1, F2, F3, F4
Col1 , Col2 , Col3 , Col4
A, 1, Australia, CC
A, 1, India, AA
A, 1, US, BB
B, 3, Brazil, GG
B, 3, India, DD
B, 3, Japan, FF
];

Load
F1, F2,
concat(F3, ',') as gF3,
concat(F4, ',') as gF4
Resident Data
group by
F1, F2;

exit script;

[manideep78]

Hi @QFabian ,

Thank you for your quick response. I appreciate it. 

I want to achieve the above output in just Pivot table expressions. 

I just put my requirement in a very simple way whereas my actual requirement has more dimensions and expressions in Pivot table. so, I decided not to confuse with too many fields.

also, the above script will not give the actual sort order I needed for the col4.

To keep it simple and clear

Col4 concatenate sort order should be same as Col3 concatenate sort order. 

[QFabian]

Sorry!, now is what you want !

Data:
LOAD * INLINE [
Col1 , Col2 , Col3 , Col4
A, 1, Australia, CC
A, 1, India, AA
A, 1, US, BB
B, 3, Brazil, GG
B, 3, India, DD
B, 3, Japan, FF
];

Data2:
Load
recno() as id,
Col1 & '|' & Col2 as %_Key,
Col3,
recno() & '' & Col4 as Col4
Resident Data;

drop table Data;

Data3:
Load
%_Key,
concat(Col3, ', ') as gCol3,
purgechar(concat(Col4, ', '), '1234567890') as gCol4
Resident Data2
Group By
%_Key;

exit script;

[manideep78]

Is there a way I can achieve this using Pivot table expressions not in Script?

[QFabian]

you should always think on put complexity in script.

[QFabian]

but yeah, just have to add the red script, to be able of order the data :

Data:
LOAD * INLINE [
Col1 , Col2 , Col3 , Col4
A, 1, Australia, CC
A, 1, India, AA
A, 1, US, BB
B, 3, Brazil, GG
B, 3, India, DD
B, 3, Japan, FF
];

Data2:
Load
recno() as id,
*
Resident Data;

and then use this expression  :

purgechar(concat(id & '' & Col4, ', '), '1234567890')

[manideep78]

Hi @QFabian ,

Thank you again for your response.

That expression is not giving me the correct sort order. 

and yeah sorry, my ID column contains characters as well. Apologies, I forgot to mention this earlier.