Qlik Community

App Development

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

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edrean
Contributor
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
mhaferkamp
Partner
Partner

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
mhaferkamp
Partner
Partner

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