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 lavi,

I want to know that this all are fields that you fetch

    [38899.000000],

     [38991.000000],

     [39083.000000],

     [39173.000000],

     [39264.000000],

Do you have any sample if so please provide it goes easy.

Regards,

Anand

[its_anandrjs]

Hi Lavi,

I think you need to convert the field into Date type column.

Regards,

Anand

[SunilChauhan]

Tab1:

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')

));

Tab2:

Load

date(Date) as Date,

Quota,

EmployeeID

resident Tab1;

drop table Tab1;

[harleen_singh]

sunil,

        i have already tried this

Tab2:

Load

date(Date) as Date,

Quota,

EmployeeID

resident Tab1;

drop table Tab1;

it is not converting to date here. Even in front end date(Date) is not working.

my question is why it is not retrieving to as usual date format?

thanks

Lavi(Harlin)

[its_anandrjs]

Hi Lavi,

Also check the date column in your excel file it is correct or not,if you share any sample it goes easy. After that you need to make resident table and convert column into date field.

Regards,

Anand

[its_anandrjs]

Hi Lavi,

Try to convert column into date by this

     Date([Evaluation Date],'MM/DD/YYYY') as Date_Col

Regards,

Anand

[harleen_singh]

Anand,

           i don't want to change date column in excel file. If that way i wanted to do  i can also change the whole crosstable to normal table.

i don't want to change anything in  excel.

i have checked it is in date format too

regards

Lavi

[its_anandrjs]

Hi Lavi,

Not at all you do changes in excel i say do this on resident load like this,

Table1:

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')

));

Table2:

Load

Date(Date,'MM/DD/YYYY') as Date,

Quota,

EmployeeID

resident Table1;

drop table Table1;

By this you do change to your column

Hope this helps you

Regards,

Anand

[harleen_singh]

Anand,

             i have tried ur code already it  is not working. date is showing blank values.

thanks

lavi