Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I used a crosstable transformation to transform my pivot table. One of the new column which is supposed to be cadences is writen as a date and not as a number.
I don't manage to transform it as a general format.
My code is written below
tabCadence:
CrossTable (DateCadence,Cadence , 4) load *;
LOAD *
FROM [lib://Qlik Essai1/cadences_test.xlsx]
(ooxml, embedded labels, table is Feuil1);
NoConcatenate
load Programme,
Client,
Code,
DateCadence,
num#(Cadence) as Cadence;
LOAD * Resident tabCadence;
Drop table tabCadence;
Do you have an idea ?
My raw data are attached
Thanks
Hi Caroline,
I guess your requirements needs a little bit more of elaboration.
However, for a start please do the following and investigate:
tabCadence:
CrossTable (Cadence ,DateCadence, 4) load *;'
Your existing definition is leading to incorrect data format.
Happy to help further.
Also check this link : Working with crosstables in the data load script ‒ Qlik Sense
Thanks,
Alip
Did you try adding Date() function to see if it works?
tabCadence:
CrossTable (DateCadence,Cadence , 4)
LOAD *
FROM [lib://Qlik Essai1/cadences_test.xlsx]
(ooxml, embedded labels, table is Feuil1);
NoConcatenate
LOAD Programme,
Client,
Code,
DateCadence,
Date(Num#(Cadence)) as Cadence
Resident tabCadence;
Looks like its with the data format in the Excel Source file. It is being loaded as date for some reason.
Since, the data is not under your control I would suggest formatting it while loading into the cross table as below:
CrossTable (DateCadence,Cadence , 4) load *;
LOAD
Programme ,Client ,Code, "Nb mois", num("01/01/2018",'#.#0') as "01/01/2018",
num("01/02/2018",'#.#0') as "01/02/2018"
FROM [lib://AttachedFiles/cadences_test.xlsx]
(ooxml, embedded labels, table is Feuil1);
This resolves the format issue and here its done for only two of the columns.
Thanks a lot ! I changed the format in the excel document and it is working