topic Re: Concat() in PIVOT table based on a Sort weight in QlikView

Concat() in PIVOT table based on a Sort weight

manideep78 — Sat, 16 Nov 2024 17:49:52 GMT

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.

Re: Concat() in PIVOT table based on a Sort weight

QFabian — Fri, 13 Nov 2020 16:08:24 GMT

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;

Re: Concat() in PIVOT table based on a Sort weight

manideep78 — Fri, 13 Nov 2020 16:18:30 GMT

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.

Re: Concat() in PIVOT table based on a Sort weight

QFabian — Fri, 13 Nov 2020 16:41:14 GMT

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;

Re: Concat() in PIVOT table based on a Sort weight

manideep78 — Fri, 13 Nov 2020 16:44:31 GMT

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

Re: Concat() in PIVOT table based on a Sort weight

QFabian — Fri, 13 Nov 2020 16:46:19 GMT

you should always think on put complexity in script.

Re: Concat() in PIVOT table based on a Sort weight

QFabian — Fri, 13 Nov 2020 16:52:13 GMT

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')

Re: Concat() in PIVOT table based on a Sort weight

manideep78 — Fri, 13 Nov 2020 17:09:37 GMT

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.