Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
Highlighted
gireesh1216
Contributor 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
MVP
MVP

Re: Cross Table for multiple questions

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
3 Replies
MVP
MVP

Re: Cross Table for multiple questions

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

Re: Cross Table for multiple questions

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;
Community Manager
Community Manager

Re: Cross Table for multiple questions

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
Community Browser