Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Marcus,
no I tried that already as well
and it doesn't work either
thank you for answering anyway
chris
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.
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
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
Swuehl
the XL file is not the input fiel but output file from QV
Find attached an example of th einput date format
Chris
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
Marcus
keepchar doesn't work on date format
chris
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