topic Re: CrossTable query in QlikView

CrossTable query

DeeptiRao — Sat, 16 Nov 2024 00:54:52 GMT

Hi All,

I have data in below format

Name	Address
A	ABC
A	XYZ
A	DEF
B	JKL
B	OPQ
C	ABC
D	XYZ
E	DEF
F	JKL
F	OPQ
F	ABC

and the result should be in below format. So basically getting all the values for first column together.

A	B	C	D	E	F
ABC	JKL	ABC	XYZ	DEF	JKL
XYZ	OPQ				OPQ
DEF					ABC

I tried using cross table, but the result is not correct.

Kindly help to resolve this issue.

Re: CrossTable query

Vegar — Thu, 02 Apr 2020 20:52:39 GMT

You can use Generic Load to solve this problem.

Take a look at @hic good old blog post named The Generic Load.

-Vegar

Re: CrossTable query

DeeptiRao — Tue, 07 Apr 2020 09:45:45 GMT

Hi Vegar,

I tried the solution recommended by you, I am getting the result, but not exactly same. The rows are getting duplicated.

I am attaching the the qvw file, i/p and o/p file.

Kindly help.

Thanks & Regards,

Deepti

Re: CrossTable query

Vegar — Tue, 07 Apr 2020 14:08:30 GMT

I missunderstood your requirement. I had a second look and attaching a suggested solution (using your previous attached i/p excel file).

See my output in the below image.

CROSS_DATA: LOAD PS_SERIES, PS_MATERIAL_TYPE FROM [CROSS_DATA.xlsx] (ooxml, embedded labels, table is CROSS_DATA) ; Data: LOAD * inline[TMP_ID]; FOR each _serie in fieldvaluelist('PS_SERIES') JOIN (Data) LOAD RowNo() as TMP_ID, PS_MATERIAL_TYPE as [$(_serie)] Resident CROSS_DATA WHERE PS_SERIES = '$(_serie)' ; NEXT DROP Table CROSS_DATA; //Do not need this source anymore DROP FIELD TMP_ID; //Do not need this field

Re: CrossTable query

DeeptiRao — Wed, 08 Apr 2020 04:48:22 GMT

The solution is working correctly.

Thanks a Lot Vegar.