Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
Try to use Num(Trim(YearMonth)) . I faced the same problem and it got solved by using this code.
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.
Hi ,
Try
Transction1:
Load * Resident Transaction;
Concatenate
Load
Num(Trim(YearMonth)) AS YearMonth,
Value,Budget
Resident Budget;
Drop Table Transaction;
can u share sample excel
excel file
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
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!