Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Edrean
New Contributor

Crosstable for multiple column groups

Hi

I have data like this :Capture.JPG

 

 

and I would like to have it like this :

Capture.JPG

You will notice that the number after each category (eg. Colour1) signifies the row number on the order.

I have tried using crosstable, but I cannot seem to get it right. I will appreciate your help!

I will also add an Excel spreadsheet with the data.

1 Solution

Accepted Solutions
Partner
Partner

Re: Crosstable for multiple column groups

Hi Edrean,

please try following script:

TMP:
LOAD
	*
From Your Excel;

Facts:
LOAD
	Null() as Type
AutoGenerate 0;

For i =1 to 3
	
	let vType = 'Type' & $(i);
	let vColour = 'Colour' & $(i);
	let vSize = 'Size' & $(i);
	let vQty = 'Qty' & $(i);

	Concatenate(Facts)
	LOAD
		Date,	
		Client,	
		[Order no],	
		$(vType) as Type,
		$(vColour) as Colour,
		$(vSize) as Size,
		$(vQty) as Qty
	Resident TMP
	where len(trim($(vType))) > 0;
	
	vType = null();
	vColour = null();
	vSize = null();
	vQty = null();

next i;

Drop Tables TMP;

I hope I was able to help you. Best regards,

Marius.

View solution in original post

1 Reply
Partner
Partner

Re: Crosstable for multiple column groups

Hi Edrean,

please try following script:

TMP:
LOAD
	*
From Your Excel;

Facts:
LOAD
	Null() as Type
AutoGenerate 0;

For i =1 to 3
	
	let vType = 'Type' & $(i);
	let vColour = 'Colour' & $(i);
	let vSize = 'Size' & $(i);
	let vQty = 'Qty' & $(i);

	Concatenate(Facts)
	LOAD
		Date,	
		Client,	
		[Order no],	
		$(vType) as Type,
		$(vColour) as Colour,
		$(vSize) as Size,
		$(vQty) as Qty
	Resident TMP
	where len(trim($(vType))) > 0;
	
	vType = null();
	vColour = null();
	vSize = null();
	vQty = null();

next i;

Drop Tables TMP;

I hope I was able to help you. Best regards,

Marius.

View solution in original post