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

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

harleen_singh — Tue, 09 Aug 2011 08:49:35 GMT

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

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

its_anandrjs — Tue, 09 Aug 2011 09:00:04 GMT

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

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

its_anandrjs — Tue, 09 Aug 2011 09:01:45 GMT

Hi Lavi,

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

Regards,

Anand

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

SunilChauhan — Tue, 09 Aug 2011 09:09:52 GMT

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;

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

harleen_singh — Tue, 09 Aug 2011 09:25:52 GMT

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)

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

its_anandrjs — Tue, 09 Aug 2011 09:28:32 GMT

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

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

its_anandrjs — Tue, 09 Aug 2011 09:31:32 GMT

Hi Lavi,

Try to convert column into date by this

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

Regards,

Anand

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

harleen_singh — Tue, 09 Aug 2011 09:36:15 GMT

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

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

its_anandrjs — Tue, 09 Aug 2011 09:50:28 GMT

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

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

harleen_singh — Wed, 10 Aug 2011 11:52:53 GMT

Anand,

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

thanks

lavi

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

its_anandrjs — Wed, 10 Aug 2011 12:38:41 GMT

Hi,

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

Regards,

Anand

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

SunilChauhan — Wed, 10 Aug 2011 12:41:25 GMT

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

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

simon_hallworth — Wed, 10 Aug 2011 12:47:44 GMT

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

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

harleen_singh — Thu, 11 Aug 2011 09:15:12 GMT

Thank u so much simon it worked.

thanks

Lavi