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: 
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 - Contributor II
Partner - Contributor II

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 - Contributor II
Partner - Contributor II

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.