Cross Table for multiple questions

[gireesh1216]

Hello Team,

How to create multiple cross table for below table.

EMPID	NAME	Q1	Q2	C1	C2	C3	D1	D2
1216	GIRI	10	5	8	8	7	7	9
1220	SREE	15	8	9	3	8	5	5
1219	HARI	4	6	6	2	9	3	1

Output:

EMPID	NAME	Q	C	D	QDATA	CDATA	DDATA
1216	GIRI	Q1	C1	D1	10	8	7
1216	GIRI	Q2	C2	D2	5	8	9
1216	GIRI		C3			7
1220	SREE	Q1	C1	D1	15	9	5
1220	SREE	Q2	C2	D2	8	3	5
1220	SREE		C3			8
1219	HARI	Q1	C1	D1	4	6	3
1219	HARI	Q2	C2	D2	6	2	1
1219	HARI		C3			9

Thanks,

Gireesh

[jonathandienst · Accepted solution]

Like this (replace Inline with actual data source):

SourceData:
LOAD * INline
[
MPID	NAME	Q1	Q2	C1	C2	C3	D1	D2
1216	GIRI	10	5	8	8	7	7	9
1220	SREE	15	8	9	3	8	5	5
1219	HARI	4	6	6	2	9	3	1
] (delimiter is '\t');

T1:
CrossTable(T_Col, T_Val, 2)
LOAD * Resident SourceData;

T2:
LOAD MPID, NAME, 
	T_Col as [Q],
	T_Val as QDATA,
	PurgeChar(T_Col, 'QCD') as TID
Resident T1
Where T_Col Like 'Q*'
Order By MPID, [T_Col];

Join (T2)
LOAD MPID, NAME, 
	T_Col as [C],
	T_Val as CDATA,
	PurgeChar(T_Col, 'QCD') as TID
Resident T1
Where T_Col Like 'C*'
Order By MPID, [T_Col];

Join (T2)
LOAD MPID, NAME, 
	T_Col as [D],
	T_Val as DDATA,
	PurgeChar(T_Col, 'QCD') as TID
Resident T1
Where T_Col Like 'D*'
Order By MPID, [T_Col];

DROP Table SourceData, T1;

[sunny_talwar]

Another way to do this

Table:
CrossTable (LABEL, VALUE, 2)
LOAD * INLINE [
    EMPID, NAME, Q1, Q2, C1, C2, C3, D1, D2
    1216, GIRI, 10, 5, 8, 8, 7, 7, 9
    1220, SREE, 15, 8, 9, 3, 8, 5, 5
    1219, HARI, 4, 6, 6, 2, 9, 3, 1
];

TempTable:
LOAD EMPID,
	 NAME,
	 LABEL,
	 Left(LABEL, 1) as LABEL1,
	 Right(LABEL, 1) as LABEL2,
	 VALUE
Resident Table;

DROP Table Table;

FinalTable:
LOAD DISTINCT EMPID,
	 NAME,
	 LABEL2
Resident TempTable;

FOR i = 1 to FieldValueCount('LABEL1')

	LET vLabel1 = FieldValue('LABEL1', $(i));
	
	Left Join (FinalTable)
	LOAD DISTINCT EMPID,
		 NAME,
		 LABEL2,
		 LABEL as [$(vLabel1)],
		 VALUE as [$(vLabel1)DATA]
	Resident TempTable
	Where LABEL1 = '$(vLabel1)';
	
NEXT

DROP Table TempTable;

[Sue_Macaluso]

Hi is this for QlikView or Qlik Sense? I would like to this into the product forum as this is a general discussion forum. Thank you