Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to use excels as development sources. Access to the database is not possible.
These excels are created by exports from a sql server view.
The latest excel I received loads dates and number different from previous excels and therefore the data is not calculated right. Also, special characters are shown different.
I suspect this has something to do with the export parameters to excel.
Or is this a QlikView issue and if so, how can I solve this?
Examples:
The dates as of 30-11-2014 are from the latest excel file.
Hi,
I would suggest here to define in your script date format , this will automatically take care of one single format.
Date(Your_Datefiel_Field,'MM/DD/YYYY') as Date_Key
Thanks,
AS
In your table you can also do like below:
Thanks,
AS
Hi,
When you are loading the data format the date field like below
LOAD
*,
Date(Date#(Your_Datefiel_Field,'DD-MM-YYYY')) as Date_Formatted
FROM DataSource;
Now use this field for export.
Regards,
Jagan.
The dates from the latest excel file look like proper dates. The earlier ones look like text strings. If your sources change their date formats that's not a problem Qlikview caused. But you most likele can handle the issue in Qlikview by using the alt function: date(alt(MyExcelDate,date#(MyExcelDate,'DD-MM-YYYY'),'DD-MM-YYYY') as MyDate.
The special characters like ë is something you should take care of in the source. If your excel file is really an excel file (and not a csv file masquerading as an excel file with a bogus .xls suffix) then the text values from the source database were not correctly extracted. It looks like unicode strings were exported as ascii strings.
Hi
Use Date# Function which is a Interpretation function.
Date# will convert string to number, i.e. the input is a string that contains a date and the function creates a correct date serial number. The output is a dual field, i.e. both string and number.
Hope that helps.
Regards
Av7eN