Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
Yikes, that is quite the data structure.
If your data looks exactly like your sample, the method below should work.
It is a bit of a journey, but will get your there regardless of whether new field "pairs" are added in the future.
Based on your data, I made the assumption the first field is ALWAYS the product name and the there is one row per product. (it doesn't matter what the product is called)
This in relevant to the solution below.
//load the date, assume each product as it's own row, assign it a number
InitalLoad:
LOAD rowno() as ProductID,
*
FROM [lib://DataFolder/ExcelData/Community/doublecrosstab.xlsx] //<--the this needs to be your file
(ooxml, embedded labels, table is Sheet1);
//get the name of the first field in the orginal file
//which is the second field of the InitalLoad Table due to the ProductID column
LET vProduct = FieldName(2 ,'InitalLoad');
//create a table to associate the product name with the productID
FinalTable:
LOAD [$(vProduct)] as Product,
ProductID
Resident InitalLoad;
//unpivot the InitalLoad table, create a new table
CrossTabTemp:
CrossTable('Attribute', 'Value', 1)
load *
Resident InitalLoad;
//InitalLoad table no longer needed.
Drop table InitalLoad;
/************************************************************************
Because the original file has dupilcate field names, Qlik should
automatically append the number 1 to the duplciate date (ex. 01/07/20201)
This table filters for only those fields with more than 10 chars. (hese fields
will contain TRUE or FALSE.) it also converts them into dates
************************************************************************/
CrossTab:
NoConcatenate
Load
ProductID,
Date#(Left(Attribute,10),'DD/MM/YYYY') as [Date],
Value as Warehouse
Resident CrossTabTemp
WHERE len(Attribute)>10; //<--the filter
//we don't need CrossTabTemp anymore
Drop Table CrossTabTemp;
//join CrossTabl into FinalTable
Left Join (FinalTable)
Load * Resident CrossTab;
//we no longer need CrossTab
Drop Table CrossTab;
Result:
Yikes, that is quite the data structure.
If your data looks exactly like your sample, the method below should work.
It is a bit of a journey, but will get your there regardless of whether new field "pairs" are added in the future.
Based on your data, I made the assumption the first field is ALWAYS the product name and the there is one row per product. (it doesn't matter what the product is called)
This in relevant to the solution below.
//load the date, assume each product as it's own row, assign it a number
InitalLoad:
LOAD rowno() as ProductID,
*
FROM [lib://DataFolder/ExcelData/Community/doublecrosstab.xlsx] //<--the this needs to be your file
(ooxml, embedded labels, table is Sheet1);
//get the name of the first field in the orginal file
//which is the second field of the InitalLoad Table due to the ProductID column
LET vProduct = FieldName(2 ,'InitalLoad');
//create a table to associate the product name with the productID
FinalTable:
LOAD [$(vProduct)] as Product,
ProductID
Resident InitalLoad;
//unpivot the InitalLoad table, create a new table
CrossTabTemp:
CrossTable('Attribute', 'Value', 1)
load *
Resident InitalLoad;
//InitalLoad table no longer needed.
Drop table InitalLoad;
/************************************************************************
Because the original file has dupilcate field names, Qlik should
automatically append the number 1 to the duplciate date (ex. 01/07/20201)
This table filters for only those fields with more than 10 chars. (hese fields
will contain TRUE or FALSE.) it also converts them into dates
************************************************************************/
CrossTab:
NoConcatenate
Load
ProductID,
Date#(Left(Attribute,10),'DD/MM/YYYY') as [Date],
Value as Warehouse
Resident CrossTabTemp
WHERE len(Attribute)>10; //<--the filter
//we don't need CrossTabTemp anymore
Drop Table CrossTabTemp;
//join CrossTabl into FinalTable
Left Join (FinalTable)
Load * Resident CrossTab;
//we no longer need CrossTab
Drop Table CrossTab;
Result:
