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.
Not applicable

Problem with data format interpretation

Hi everybody

I am currently facing a weird problem with dates

I have several input XL files having a data like 15/05/1962  (XL cell format is Date like *14/03/2001)

When I read the date in QlikView I get weird behavior like very date is interpreted like if the format was DD/MM/YY giving 15/05/62 instead, so the date is transformed as 15/05/2062

The limit is more than 50 years ago. In this case all dates minor than 1965 are transformed into 20xx

For dates over 1965, the year remains the same

I did some test like ear(DT_NAI) as year      and this gives me 2062 but date is value is correctly evaluated as a date format

I tried some text(), date() format interpretations but I still face the problem

More than that some files (having exactly the same ID and same date format) are normally interpreted, some are not

I guess there is something I forgot to think about

Here are data loaded by Qlik and exported into XL file.

DT_NAI is original data (XL cell format *14/03/2001)

other fields are transformed by Qlik like

LOAD

filename() as filesource,
DT_NAI,
year(DT_NAI) as y,
right(DT_NAI, 4) as rightdate,
mid(DT_NAI, 4, 2) as middate,
Text(DT_NAI) as Date_TXT,
date(DT_NAI) as [Date de naissance],
date(DT_NAI, 'DD/MM/YYYY') as [Date de naissance DDMMYYYY],
makedate(year(DT_NAI), num(month(DT_NAI)), day(DT_NAI)) as Remake_Date
FROM

Any clue will be highly appreciated

best regards

Chris

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Problem with data format interpretation

After some research, I found that just re-save the file changes its format.

I don't understand why, but it solves the problem.

it seems it is more a XL problem than a QV date format problem

I guess some files were saved under different MS office versions.

I don't have no more explanations about this weird behaviour

thank you for your participation

best regards

Chris

9 Replies
marcus_malinow
Valued Contributor III

Re: Problem with data format interpretation

Ok try this:

LOAD

filesource,

DT_NAI,
year(DT_NAI) as y,
right(DT_NAI, 4) as rightdate,
mid(DT_NAI, 4, 2) as middate,
Text(DT_NAI) as Date_TXT,
date(DT_NAI) as [Date de naissance],
date(DT_NAI, 'DD/MM/YYYY') as [Date de naissance DDMMYYYY],
makedate(year(DT_NAI), num(month(DT_NAI)), day(DT_NAI)) as Remake_Date

;

LOAD

filename() as filesource,

date(date#(DT_NAI, 'DD/MM/YYYY')) as DT_NAI

FROM

Not applicable

Re: Problem with data format interpretation

Hi Marcus,

no I tried that already as well

and it doesn't work either

thank you for answering anyway

chris

MVP
MVP

Re: Problem with data format interpretation

If I understood correctly, that issue is not weird, but QV default.

If you let QV automatically interpret a two digit year string as year, it will behave like observed, taking the two digits as last two digits and using a +-50 year range around current year to decide the century.

A two digit year is ambiguous, after all, so if you know the century, you should let QV know when reading in the data.

Not applicable

Re: Problem with data format interpretation

Hi Swuehl

I understood the QV default behaviour

What i don't understand is the interpretation of a raw date given as 15/05/1962  and transformed (how ?) into 15/05/62  when read, and then for sure the default is applied and gives 15/05/2062  which is wrong

best regards

Chris

MVP
MVP

Re: Problem with data format interpretation

I can't see your issue with the data and the above load script.

In your LOAD script, you don't read the dates correctly in (as far as I see, DT_NAI in your excel is a text value, so you need to use

Date#(DT_NAI, 'DD/MM/YYYY')

to interpret the values as dates).

edit:

See also

Get the Dates Right

Why don’t my dates work?

Not applicable

Re: Problem with data format interpretation

Swuehl

the XL file is not the input fiel but output file from QV

Find attached an example of th einput date format

Chris

marcus_malinow
Valued Contributor III

Re: Problem with data format interpretation

Christian,

really not sure what's going on here - I am also having issues with your data. However, this should work:

Date(keepchar(DT_NAI, '0123456789')) as DT_NAI

Then apply all your calculations to this.

Marcus

Not applicable

Re: Problem with data format interpretation

Marcus

keepchar doesn't work on date format

chris

Not applicable

Re: Problem with data format interpretation

After some research, I found that just re-save the file changes its format.

I don't understand why, but it solves the problem.

it seems it is more a XL problem than a QV date format problem

I guess some files were saved under different MS office versions.

I don't have no more explanations about this weird behaviour

thank you for your participation

best regards

Chris

Community Browser