Not able convert date in date format while retrieving date from crosstable

[harleen_singh]

Hello this is my code when i am loading data from crosstable

CrossTable(Date, Quota)

LOAD EmployeeID,

     [38899.000000],

     [38991.000000],

     [39083.000000],

     [39173.000000],

     [39264.000000],

     [39356.000000],

     [39448.000000],

     [39539.000000],

     [39630.000000],

     [39722.000000],

     [39814.000000],

     [39904.000000],

     [39995.000000]

FROM

Table

(ooxml, embedded labels, table is Quota, filters(

Transpose(),

Top(1, 'EmployeeID')

));

where [38899.000000] like Fields are generated automatically by qlikview from dates stored in crosstable in this format 1/1/2007

how to get this as a orginal date instead of this number?

i date function in front end is not working here

thanks

Lavi

[its_anandrjs]

Hi,

Do you have any sample file if so please share it, it goes so easy.

Regards,

Anand

[SunilChauhan]

CrossTable(Date, Quota,1) intead of CrossTable(Date, Quota,1)

[simon_hallworth · Accepted solution]

Hi Lavi,

The problem is that the crosstable prefix forces the contents of the Date field to be in text format. In order to get the values back to dates you need to first use the Num#() function, then you can use Date() on the result. So extending on Anand's code...

Table2:
NOCONCATENATE LOAD
  Date(Num#(Date)) AS Date,
  Quota,
  EmployeeID
RESIDENT Table1;

DROP TABLE Table1;

Kind regards,

Simon

[harleen_singh]

Thank u so much simon it worked.

thanks

Lavi