Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Data appears in date format instead of number format after crosstable transformation

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

4 Replies
Olip
Creator
Creator

Hi Caroline,

I guess your requirements needs a little bit more of elaboration.

However, for a start please do the following and investigate:

  • Update definition as '

tabCadence:

CrossTable (Cadence ,DateCadence, 4) load *;'

  • Check the resulting table data first and take your next steps

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

- Eager to learn n grow,

Olip
sunny_talwar

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;

Olip
Creator
Creator

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.

- Eager to learn n grow,

Olip
Anonymous
Not applicable
Author

Thanks a lot ! I changed the format in the excel document and it is working