topic text to numeric in QlikView

text to numeric

Anonymous — Wed, 11 Jan 2012 12:29:54 GMT

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!

text to numeric

vijay_iitkgp — Wed, 11 Jan 2012 12:35:19 GMT

Hi,

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

text to numeric

Anonymous — Wed, 11 Jan 2012 12:56:31 GMT

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.

text to numeric

vijay_iitkgp — Wed, 11 Jan 2012 13:00:46 GMT

Hi ,

Try

Transction1:

Load * Resident Transaction;

Concatenate

Load

Num(Trim(YearMonth)) AS YearMonth,

Value,Budget

Resident Budget;

Drop Table Transaction;

text to numeric

SunilChauhan — Wed, 11 Jan 2012 13:01:49 GMT

can u share sample excel

Re: text to numeric

Anonymous — Wed, 11 Jan 2012 13:14:12 GMT

excel file

text to numeric

SunilChauhan — Wed, 11 Jan 2012 13:19:00 GMT

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

Re: text to numeric

Anonymous — Wed, 11 Jan 2012 13:30:04 GMT

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!