Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

demaina1995
New Contributor II

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

Tags (1)
4 Replies
alip_nath
Contributor

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

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

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

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;

alip_nath
Contributor

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

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.

demaina1995
New Contributor II

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

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

Community Browser