how to convert [2003.000000] in year

[#1]

how to convert [2003.000000] in year 2003

[Anonymous]

Is there a reason why you do not just alias ?

[#3]

Thanks Peter,

Its working.

[#4]

Hi,

I got the solution.was looking for other method. Thanks !

[its_anandrjs]

Hi,

If after cross table you get values like 2003.000000

tmp:

CrossTable(year, amount, 2)

LOAD Office,

     Department,

     [2003.000000],

     [2004.000000],

     [2005.000000],

     [2006.000000],

     [2007.000000]  

FROM [..\ct2.xlsx] (ooxml, ...);

Final:

NOCONCATENATE

LOAD Office, Department,

    NUM(year,'####') as Year1, //First Try

     Year(Date#(NUM(year,'####'),'YYYY')) as Year2, //Second Try

     amount

RESIDENT tmp;

DROP Table tmp;

OR provide sample file for this and let me know

Regards

Anand

[MarcoWedel]

t1:

CrossTable(yeartemp, amount, 2)

LOAD *

FROM

[..\ct2.xlsx];

Left Join (t1)

LOAD Distinct

          yeartemp,

           TextBetween(yeartemp, '[', '.') as year

Resident t1;

hope this helps

regards

Marco