Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi evrybody,
I am facing a strange issue, I should nromally not (according to myself)
I have dates in XL File (date format cells)
When I load it to QV i get numbers (OK) but I can't tranform it under date format.
I will appreciate your knowledge to solve this
here are the files
bedt regards
juju
At what point of time in your script execution are you trying to parse the date?
Could you post your script?
As I said above, the CROSSTABLE will transform all column labels to text, and the column label is a textual number like 41030 when read from excel. so trying to interpret it with a date format will not work.
Use Trim() function on resident load
T:
CrossTable(Month, Value, 3)
LOAD Country,
Specialty,
Brand,
[40940],
[40969],
[41000],
[41030],
[41061],
[41091],
[41122],
[41153],
[41183],
[41214],
[41244]
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);
T2:
NoConcatenate
LOAD *,
trim(Month) as MONTH
Resident T;
DROP Table T;
Now MONTH will have Number format
try like this:
=Date(Num#(Month),'DD/MM/YYYY')
if you want month name :
Monthname(Num#(Month)) as Month_Name
That's an odd behaviour of the CROSSTABLE LOAD prefix, columns labels will always be transformed to text values.
You'll need a resident load to correct for this:
T:
CrossTable(Month, Value, 3)
LOAD Country,
Specialty,
Brand,
[40940],
[40969],
[41000],
[41030],
[41061],
[41091],
[41122],
[41153],
[41183],
[41214],
[41244]
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);
NOCONCATENATE
LOAD Country, Specialty, Brand, MonthName(num#(Month)) as Month, Value
RESIDENT T;
DROP TABLE T;
Try like this
=date(Num#(Month))
or use below code
T:
CrossTable(Month, Value, 3)
LOAD Country,
Specialty,
Brand,
[40940],
[40969],
[41000],
[41030],
[41061],
[41091],
[41122],
[41153],
[41183],
[41214],
[41244]
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);
//EXIT Script;
NoConcatenate
t1:
Load
Date(num#(Month)) as Date,
Value,
Country,
Specialty,
Brand
Resident T;
DROP Table T;
Regards
ASHFAQ
Hi,
try this
Regards
Hi Swuehl,
Thank you taht works.
Just explain why Trilm is needed because there is apparently no blank
Juju
Hi Ashfaq,
thank you it works too.
What I tried was date(data#(Month, 'DD/MM/YY))
Why num# and Not date# ?
Juju
Hi Avinash,
thank you it works too.
What I tried was date(data#(Month, 'DD/MM/YY))
Why num# and Not date# ?
Juju
Hi Swuehl,
Thank you taht works.
Just explain why Trilm is needed because there is apparently no blank
Juju
I haven't used trim() and I don't think it's necessary.
Trim() function was suggested in the first answer, by Kush141087