Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem with the table transformation in the script in Qlik.
I have the table (below) with the days of the month in the row and the products in the columns.
01/07/2020 | 01/07/2020 | 02/07/2020 | 02/07/2020 | 03/07/2020 | 03/07/2020 | 04/07/2020 | 04/07/2020 | ... | ... | 31/12/2020 | 31/12/2020 |
Product1 | FALSE | Product1 | TRUE | Product1 | FALSE | Product1 | FALSE | Product1 | FALSE | Product1 | TRUE |
Product2 | FALSE | Product2 | FALSE | Product2 | FALSE | Product2 | FALSE | Product2 | FALSE | Product2 | FALSE |
Product3 | FALSE | Product3 | FALSE | Product3 | FALSE | Product3 | FALSE | Product3 | FALSE | Product3 | FALSE |
Product4 | TRUE | Product4 | TRUE | Product4 | TRUE | Product4 | FALSE | Product4 | FALSE | Product4 | TRUE |
Such a structure cannot be analyzed.
I need to get the structure as below, i.e. dates, products and false / true in separate columns.
Date | Product Name | Warehouse |
01/07/2020 | Product1 | FALSE |
02/07/2020 | Product1 | TRUE |
03/07/2020 | Product1 | FALSE |
04/07/2020 | Product1 | FALSE |
... | Product1 | FALSE |
31/12/2020 | Product1 | TRUE |
01/07/2020 | Product2 | FALSE |
02/07/2020 | Product2 | FALSE |
03/07/2020 | Product2 | FALSE |
04/07/2020 | Product2 | FALSE |
... | Product2 | FALSE |
31/12/2020 | Product2 | FALSE |
01/07/2020 | Product3 | FALSE |
02/07/2020 | Product3 | FALSE |
03/07/2020 | Product3 | FALSE |
04/07/2020 | Product3 | FALSE |
... | Product3 | FALSE |
31/12/2020 | Product3 | FALSE |
01/07/2020 | Product4 | TRUE |
02/07/2020 | Product4 | TRUE |
03/07/2020 | Product4 | TRUE |
04/07/2020 | Product4 | FALSE |
... | Product4 | FALSE |
31/12/2020 | Product4 | TRUE |
I tried with a cross table but this is only a data sample and there you have to enter the name of each column.
Ultimately there will be as many columns as there are days in the year. 01/01/2020 - 31/12/2020 etc.
Can anyone help me?
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:
How are you getting this table into Qlik? I tried creating an Inline table not thinking, and it whined about column names needing to be unique.
I have it in excel google spreadsheet and connect to qlik. Yes, there is a problem with not unique names. I can change names of columns (on date where is true/false) with letter or word.
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:
Very nice @QlikTom . Elegant solution.
Great! It works 🙂
Thank You @QlikTom !
Thanks QliKTom for solving this for us! I was at a standstill on figuring it out. I look forward to dissecting your answer to figure out the ins-and-outs of it later today.