Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gireesh1216
Creator II
Creator II

Cross Table for multiple questions

Hello Team,

How to create multiple cross table for below table.

EMPIDNAMEQ1Q2C1C2C3D1D2
1216GIRI10588779
1220SREE15893855
1219HARI4662931

 

Output:

EMPIDNAMEQCDQDATACDATADDATA
1216GIRIQ1C1D11087
1216GIRIQ2C2D2589
1216GIRI C3  7 
1220SREEQ1C1D11595
1220SREEQ2C2D2835
1220SREE C3  8 
1219HARIQ1C1D1463
1219HARIQ2C2D2621
1219HARI C3  9 

 

 

 

Thanks,

Gireesh

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
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
Community Manager
Community Manager

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
Sue Macaluso