topic SQL View export to excel, changed number formats in QlikView

SQL View export to excel, changed number formats

Michiel_QV_Fan — Mon, 22 Dec 2014 08:53:40 GMT

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.

Re: SQL View export to excel, changed number formats

amit_saini — Mon, 22 Dec 2014 09:04:08 GMT

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

Re: SQL View export to excel, changed number formats

amit_saini — Mon, 22 Dec 2014 09:06:33 GMT

In your table you can also do like below:

Thanks,
AS

Re: SQL View export to excel, changed number formats

jagan — Mon, 22 Dec 2014 09:10:02 GMT

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.

Re: SQL View export to excel, changed number formats

Gysbert_Wassenaar — Mon, 22 Dec 2014 09:13:35 GMT

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.

Re: SQL View export to excel, changed number formats

aveeeeeee7en — Mon, 22 Dec 2014 09:21:35 GMT

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