topic Re: Problem with data format interpretation in QlikView

Problem with data format interpretation

Mon, 26 Oct 2015 08:30:44 GMT

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

Re: Problem with data format interpretation

marcus_malinow — Mon, 26 Oct 2015 08:42:57 GMT

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

Re: Problem with data format interpretation

Mon, 26 Oct 2015 09:50:13 GMT

Hi Marcus,

no I tried that already as well

and it doesn't work either

thank you for answering anyway

chris

Re: Problem with data format interpretation

swuehl — Mon, 26 Oct 2015 10:00:00 GMT

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.

Re: Problem with data format interpretation

Mon, 26 Oct 2015 10:51:15 GMT

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

Re: Problem with data format interpretation

swuehl — Mon, 26 Oct 2015 11:01:52 GMT

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:

Re: Problem with data format interpretation

Mon, 26 Oct 2015 13:01:57 GMT

Swuehl

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

Find attached an example of th einput date format

Chris

Re: Problem with data format interpretation

marcus_malinow — Mon, 26 Oct 2015 13:36:08 GMT

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

Re: Problem with data format interpretation

Mon, 26 Oct 2015 13:49:22 GMT

Marcus

keepchar doesn't work on date format

chris

Re: Problem with data format interpretation

Mon, 26 Oct 2015 13:52:05 GMT

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