Crosstable for multiple column groups

[Edrean]

Hi

I have data like this :

and I would like to have it like this :

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.

[mhaferkamp · Accepted solution]

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.

[mhaferkamp · Accepted solution]

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

Marius.