Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
michielvandegoo
Valued Contributor

SQL View export to excel, changed number formats

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.

537143_2.jpg537143_1.jpg

5 Replies
amit_saini
Honored Contributor III

Re: SQL View export to excel, changed number formats

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

amit_saini
Honored Contributor III

Re: SQL View export to excel, changed number formats

In your table you can also do like below:

Thanks,
AS

MVP
MVP

Re: SQL View export to excel, changed number formats

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

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.


talk is cheap, supply exceeds demand
aveeeeeee7en
Valued Contributor III

Re: SQL View export to excel, changed number formats

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

Community Browser