topic Re: Date field Issue in QlikView

Date field Issue

Tue, 21 Oct 2014 05:29:19 GMT

Hi Friends,I have following table,

in my QV application I load above table using crosstable as follow

but when I apply date function on Periods field I am not able to get Date,Month and year from Periods field.. I am not sure is it because of crosstable I used or something else,If anyone has any idea or any suggestion please share with me,its really required..

PFA has sample application and sample xls file

Regards

Bhawna

Re: Date field Issue

ashfaq_haseeb — Tue, 21 Oct 2014 05:37:43 GMT

Hi,

Try like this

tmp:
CrossTable(Periods, Data)
LOAD Project, 
     [41640], 
     [41671], 
     [41699], 
     [41730], 
     [41760], 
     [41791], 
     [41821], 
     [41852], 
     [41883], 
     [41913], 
     [41944], 
     [41974]
FROM
test.xlsx
(ooxml, embedded labels, table is Sheet1);


load *,
date(num#(Periods,'#####')) as date,
month(num#(Periods,'#####')) as month,
year(num#(Periods,'#####')) as year
Resident tmp;
drop table tmp;

Regards

ASHFAQ

Re: Date field Issue

its_anandrjs — Tue, 21 Oct 2014 05:45:36 GMT

Hi,

Try some thing this way

tmp:

CrossTable(Periods, Data)

LOAD Project,

[41640],

[41671],

[41699],

[41730],

[41760],

[41791],

[41821],

[41852],

[41883],

[41913],

[41944],

[41974]

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

New:

NoConcatenate

load *,

Year( Num#( Periods )) as Year,

MOnth( Num#( Periods )) as Month,

Day( Num#( Periods )) as Day,

MakeDate(Year( Num#( Periods )) , Month( Num#( Periods )) , Day( Num#( Periods ))) as Date

Resident tmp;

Drop table tmp;

Regards,

Anand

Re: Date field Issue

Tue, 21 Oct 2014 05:46:02 GMT

thank you...

Re: Date field Issue

jyothish8807 — Tue, 21 Oct 2014 05:46:19 GMT

Hi Bhawana,

PFA

Regards

Re: Date field Issue

ashfaq_haseeb — Tue, 21 Oct 2014 05:47:55 GMT

Welcome

Regards

ASHFAQ