Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alternatywny
Contributor II
Contributor II

Transforming table with many columns (dates)

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/202001/07/202002/07/202002/07/202003/07/202003/07/202004/07/202004/07/2020......31/12/202031/12/2020
Product1FALSEProduct1TRUEProduct1FALSEProduct1FALSEProduct1FALSEProduct1TRUE
Product2FALSEProduct2FALSEProduct2FALSEProduct2FALSEProduct2FALSEProduct2FALSE
Product3FALSEProduct3FALSEProduct3FALSEProduct3FALSEProduct3FALSEProduct3FALSE
Product4TRUEProduct4TRUEProduct4TRUEProduct4FALSEProduct4FALSEProduct4TRUE


Such a structure cannot be analyzed.
I need to get the structure as below, i.e. dates, products and false / true in separate columns.

DateProduct NameWarehouse
01/07/2020Product1FALSE
02/07/2020Product1TRUE
03/07/2020Product1FALSE
04/07/2020Product1FALSE
...Product1FALSE
31/12/2020Product1TRUE
01/07/2020Product2FALSE
02/07/2020Product2FALSE
03/07/2020Product2FALSE
04/07/2020Product2FALSE
...Product2FALSE
31/12/2020Product2FALSE
01/07/2020Product3FALSE
02/07/2020Product3FALSE
03/07/2020Product3FALSE
04/07/2020Product3FALSE
...Product3FALSE
31/12/2020Product3FALSE
01/07/2020Product4TRUE
02/07/2020Product4TRUE
03/07/2020Product4TRUE
04/07/2020Product4FALSE
...Product4FALSE
31/12/2020Product4TRUE

 

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?

Labels (1)
1 Solution

Accepted Solutions
QlikTom
Employee
Employee

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:

QlikTom_0-1593724485442.png

QlikTom_1-1593724654052.png

 

View solution in original post

6 Replies
JustinDallas
Specialist II
Specialist II

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.

alternatywny
Contributor II
Contributor II
Author

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.

QlikTom
Employee
Employee

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:

QlikTom_0-1593724485442.png

QlikTom_1-1593724654052.png

 

Saravanan_Desingh

Very nice @QlikTom . Elegant solution.

alternatywny
Contributor II
Contributor II
Author

Great! It works 🙂

Thank You @QlikTom ! 

JustinDallas
Specialist II
Specialist II

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.