text to numeric

[Anonymous]

Hi,

I'm struggling with a yearmonth (201201) from excel where QV treats it like textfield instead or numeric. Left align instead of right. I've formatted in excel to "number" but that doesn't help. i've also tried num(yearmonth) but that didn't work either.

I'm thinking it is the crosstable's "fault". Looks like this:

Budget:

CrossTable(YearMonth, Budget)

LOAD F1 as Product,

     [201201],

     [201202],

     [201203],

     [201204],

     [201205],

     [201206],

     [201207],

     [201208],

     [201209],

     [201210],

     [201211],

     [201212]

FROM

[file.xls]

please help!

[vijay_iitkgp]

Hi,

Try to  use Num(Trim(YearMonth)) . I faced the same problem and it got solved by using this code.

[Anonymous]

ok. but i've got another problem. when reloading the crosstable in order to format it it does not work. here's my script:

Budget:

CrossTable(YearMonth, Budget)

LOAD F1 as Product,

     [201201],

     [201202],

     [201203],

     [201204],

     [201205],

     [201206],

     [201207],

     [201208],

     [201209],

     [201210],

     [201211],

     [201212]

FROM

[file.xls]

concatenate (Transactions)

Load num(trim(YearMonth)) as YearMonth,

  Value,

  Budget

Resident Budget;

drop table budget;

I don't know what i'm doing wrong.

[vijay_iitkgp]

Hi ,

Try

Transction1:

Load * Resident Transaction;

Concatenate

Load

Num(Trim(YearMonth)) AS YearMonth,

Value,Budget

Resident Budget;

Drop Table Transaction;

[SunilChauhan]

can u share sample excel

[Anonymous]

excel file

[SunilChauhan]

use this code

dummy_excel:

CrossTable(Year, Data,1)

LOAD F1 as Product,,

     [201201],

     [201202],

     [201203],

     [201204],

     [201205],

     [201206],

     [201207],

     [201208],

     [201209],

     [201210],

     [201211],

     [201212],

     F14

FROM

(biff, embedded labels, table is Blad1$);

Load *,

Monthname(makedate(left(Year,4),right(Year,2),01)) as MonthYear

resident dummy_excel;

drop table dummy_excel;

hope this helps

[Anonymous]

but i still need to format to be YYYYMM for YearMonth.

I modified it a bit like this instead:

left(xYearMonth,4)&right(xYearMonth,2) as YearMonth

and it work!

thanks for helping out! the load * was the key!