topic Re: Transforming table with many columns (dates) in App Development

Transforming table with many columns (dates)

alternatywny — Sat, 16 Nov 2024 01:59:19 GMT

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?

Re: Transforming table with many columns (dates)

JustinDallas — Thu, 02 Jul 2020 19:23:27 GMT

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.

Re: Transforming table with many columns (dates)

alternatywny — Thu, 02 Jul 2020 19:59:35 GMT

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.

Re: Transforming table with many columns (dates)

QlikTom — Thu, 02 Jul 2020 21:27:27 GMT

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:

Re: Transforming table with many columns (dates)

Saravanan_Desingh — Fri, 03 Jul 2020 01:32:47 GMT

Very nice @QlikTom . Elegant solution.

Re: Transforming table with many columns (dates)

alternatywny — Mon, 06 Jul 2020 11:12:58 GMT

Great! It works 🙂

Thank You @QlikTom !

Re: Transforming table with many columns (dates)

JustinDallas — Mon, 06 Jul 2020 13:17:39 GMT

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.