Date field Issue

[#1]

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

[ashfaq_haseeb · Accepted solution]

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

[its_anandrjs]

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

[#4]

thank you...

[jyothish8807]

Hi Bhawana,

PFA

Regards

KC

[ashfaq_haseeb]

Welcome

Regards

ASHFAQ