Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Products are sent to a location on different dates with each data a separate row in the data. I would like to have a table with first column the location, and then each column lists the product sent to that location with the first shipment being 2nd column and 2nd product shipment being 2nd column and so on.
This is the data loaded into QLIK Sense:
I would like he data to look like this:
Thank you
Hi @CB-CAN ,
I am assuming that you want this table created in the load script. The script below will create a table in that format with a dynamic number of columns created for the 1st, 2nd, 3rd, 4th and so on dates for each location. Note that in your example above, all of the dates are unique per location. If you have the same date per location then you'd have to add some additional handling (right now it would just be added as another column).
There might be a more optimal way to achieve this, this was just the first method I came up with.
Model:
Script:
OriginalData:
LOAD
Location,
Product,
Date#(Date,'D/M/YYYY') AS Date
INLINE [
Location ,Product ,Date
QB1 ,ABC ,1/1/22
CB3 ,ABC ,2/1/22
DE5 ,123 ,10/1/22
CB3 ,Zzz ,5/2/22
CB3 ,YYY ,20/2/22
QB1 ,Zzz ,3/3/22
DE5 ,ABC ,3/3/22
QB1 ,YYY ,1/5/22
];
LET i = 1;
FOR EACH Location in FieldValueList('Location')
Location$(i):
NOCONCATENATE LOAD
'$(Location)' AS Location
AUTOGENERATE 1;
Dates:
LOAD DISTINCT
Date AS DateLookup,
Product
RESIDENT OriginalData
WHERE Location = '$(Location)'
ORDER BY Date ASC;
FOR z=1 to NoOfRows('Dates')
LEFT JOIN(Location$(i))
LOAD DISTINCT
'$(Location)' AS Location,
Product AS [$(z)]
RESIDENT Dates
WHERE RecNo()=$(z);
NEXT z
LET i = $(i) + 1;
DROP TABLES Dates;
NEXT Location
Data:
LOAD * INLINE [Temp];
FOR a = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(a));
IF WildMatch('$(vTable)','Location*') THEN
CONCATENATE ([Data]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT a
DROP FIELD Temp;
Map_NumFormat:
MAPPING LOAD * INLINE [
Int ,Suffix
1 ,st
2 ,nd
3 ,rd
];
Map_RenameFields:
MAPPING LOAD
RecNo() AS Old,
RecNo() & ApplyMap('Map_NumFormat',Mod(RecNo(),10),'th') AS New
AUTOGENERATE 100;
RENAME Fields USING Map_RenameFields;
Cheers,
you can also do it using Generic Load