Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I´ve tried to fix this error but I couldn´t. The original file comes with this problem date. Excel read the field like YY/MM/DD but like final text.
Thanks.
Hi,
I could resolve the problem changing the format in:
SET DateFormat='DD-MM-YY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
Finally I left like this:
SET DateFormat='DD/MM/YY';
Looks like the data in excel is in format DD/MM/YY. That means you have to read it this way:
LOAD
date#(Date, 'DD/MM/YY') as Date
FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);
As for the DateFormat variable, you can keep it as is or change if you prefer. The date#() function tells how to interpret the data on load in QV. The variable DateFormat says how to present the data.
Regards,
Michael
Try this: Since there are two types of date formats: DD/MM/YY and MM/DD/YY
LOAD Date,alt(Date#(Date,'MM/DD/YY'),Date#(Date,'DD/MM/YY')) as FormattedDate
FROM C:\Users\pgalvez.AGROSYSTEMS\Desktop\Date.xls (biff, embedded labels, table is Sheet1$);
The FormattedDate should be correctly interpreted as date type by Qlikview and should be displayed right-aligned since Qlikview stores dates internally as numeric values.
Thanks,
Shoubhik.