9 Replies Latest reply: Oct 26, 2015 9:53 AM by christian juillard RSS

    Problem with data format interpretation

    christian juillard

      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