topic Re: CONCAT function with Sort weight in QlikView

CONCAT function with Sort weight

manideep78 — Sat, 16 Nov 2024 18:02:47 GMT

Dear Community,

I have an input like table 1 and expecting output like table2.

I would like to use Concat function without distinct but my priority is sort order. My sort order is based on Col1 and Col2 and ignore the default concat sort.

either script function or chart function is fine, but recommend in script because I have to display my output in table box.

Table-1: Input

Col1	Col2	Col3	Col4	Col5
1	A	AA	3	PQR
1	B	BB	1	ABC
1	C	CC	2	XYZ
2	X	XX	100	XXX
2	Y	YY	101	YYY
3	P	PP	200	PPP
3	P	PP	350	PPP
3	Q	QQ	250	QQQ
3	R	RR	300	RRR

Table -2: Output

Col1	Col2	Col3	Col4	Col5
1	A,B,C	AA,BB,CC	3,1,2	PQR,ABC,XYZ
2	X,Y	XX,YY	100,101	XXX,YYY
3	P,P,Q,R	PP,PP,QQ,RR	200,350,250,300	PPP,PPP,QQQ,RRR

Thanks in advance!

Re: CONCAT function with Sort weight

Anil_Babu_Samineni — Tue, 29 Sep 2020 08:14:44 GMT

PFA, IF this is what you expecting?

Re: CONCAT function with Sort weight

Taoufiq_Zarra — Tue, 29 Sep 2020 08:24:07 GMT

@manideep78 May be this version :

Input: LOAD * INLINE [ Col1, Col2, Col3, Col4, Col5 1, A, AA, 3, PQR 1, B, BB, 1, ABC 1, C, CC, 2, XYZ 2, X, XX, 100, XXX 2, Y, YY, 101, YYY 3, P, PP, 200, PPP 3, P, PP, 350, PPP 3, Q, QQ, 250, QQQ 3, R, RR, 300, RRR ]; output: noconcatenate load Col1,concat(Col2,',',recno()) as Col2,concat(Col3,',',recno()) as Col3,concat(Col4,',',recno()) as Col4,concat(Col5,',',recno()) as Col5 resident Input group by Col1; drop table Input;

output :

Re: CONCAT function with Sort weight

Taoufiq_Zarra — Tue, 29 Sep 2020 08:27:22 GMT

@manideep78 or this :

Input: LOAD *,rowno() as Numtmp INLINE [ Col1, Col2, Col3, Col4, Col5 1, A, AA, 3, PQR 1, B, BB, 1, ABC 1, C, CC, 2, XYZ 2, X, XX, 100, XXX 2, Y, YY, 101, YYY 3, P, PP, 200, PPP 3, P, PP, 350, PPP 3, Q, QQ, 250, QQQ 3, R, RR, 300, RRR ]; output: noconcatenate load Col1,concat(Col2,',',Numtmp) as Col2,concat(Col3,',',Numtmp) as Col3,concat(Col4,',',Numtmp) as Col4,concat(Col5,',',Numtmp) as Col5 resident Input group by Col1; drop table Input;

output:

Re: CONCAT function with Sort weight

manideep78 — Tue, 29 Sep 2020 20:38:06 GMT

This has the sort issue in col4, row 3.

it should be

200,350,250,300

Re: CONCAT function with Sort weight

manideep78 — Tue, 29 Sep 2020 20:41:14 GMT

I had to tweak little bit because I cannot drop the table as I have more than 100 columns and cannot mention all of them in group by.

I just took the key and the columns that need to be concatenated in a separate table using resident load and then applied the similar logic what you said. so, I can accept this as correct solution.