how to convert [2003.000000] in year

[#1]

how to convert [2003.000000] in year 2003

[MarcoWedel]

TextBetween('[2003.000000]','[','.')

[MarcoWedel]

hope this helps

regards

Marco

[ashfaq_haseeb]

Hi

try like below.

=left((purgechar('[2003.000000]','[.]')),4)

Regards

ASHFAQ

[its_anandrjs]

Hi,

Another Possible way is

LOAD
Date#(Left(KeepChar(Number,0123456789),4),'YYYY') as Year1,
Mid(Number,2,4) as Year2,*;

LOAD Number
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Regards

Anand

[maxgro]

or

=mid('[2003.000000]', 2,4)

or

=date#(mid('[2003.000000]', 2,4),'YYYY')

[#7]

hi Anand,

I m getting this data in crosstable transformation from excel...

in excel it is 2003 but in qlikview it is like

t1:

CrossTable(year, amount, 2)

LOAD Office,

     Department,

     [2003.000000] as 2003,

     [2004.000000] as 2004,

     [2005.000000] as 2005,

     [2006.000000] as 2006,

     [2007.000000] as 2007

FROM

[..\ct2.xlsx]

[Anonymous]

When you run this do you get what are after ?

If not then could share a sample xls ?

[#9]

In table box am getting same year column, and value as 2003.0000, 2004.0000 if i am not aliasing it as i done above and so on i just wanna to remove decimal part from it for reporting.

[Peter_Cammaert]

How about this (using your code and the second example that Massimo posted below):

t1:

CrossTable(year, amount, 2)

LOAD Office,

     Department,

     [2003.000000],

     [2004.000000],

     [2005.000000],

     [2006.000000],

     [2007.000000]    // Or use LOAD * if your Excel doesn't contain anything else

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

T1Temp:

NOCONCATENATE

LOAD Office, Department,

     date#(mid(year, 2, 4), 'YYYY') AS year,

     amount

RESIDENT t1;

DROP Table t1;

RENAME Table T1Temp to t1;

You can try to combine them into a single block by turning the second LOAD into a preceding load.

Best,

Peter