CrossTable query

[DeeptiRao]

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.

[Vegar]

You can use Generic Load to solve this problem.

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

-Vegar

[DeeptiRao]

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

[Vegar · Accepted solution]

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 

[DeeptiRao]

The solution is working correctly.

Thanks a Lot Vegar.