4 Replies Latest reply: Dec 13, 2015 5:47 PM by Stefan Wühl RSS

    Different date formats from different source files

      Dear Qlik Community,

       

      I am a bit confused regarding the alt function. I have the following two text files:

       

      Dates1.txt contains Dates with the format DD/MM/YYYY:

      Date

      01/01/2015

      12/01/2015

      21/01/2015

       

      Dates2.txt contains dates with the format MM/DD/YYYY:

      Date

      04/23/1984

      03/06/1984

      03/10/1984

       

      When using the alt function, it seems that QlikView is not able to interpret the different date formats correctly. The list box with the field "Date" shows:

      06.03.84

      10.03.84

      23.04.84

      01.01.15

      21.01.15

      01.12.15

       

      The last entry should be 12.01.15. What am I missing?

       

      I know, I can format the Dates separately in the code when loading the text files. But is there a way to use the alt function when loading the "DATA_WITH_ALT" table (see attachment) and get the right results?

       

      Thanks in advance,

      Olga

        • Re: Different date formats from different source files
          Stefan Wühl

          The Alt() function tries to evaluate the arguments and check on numeric return in order of appearance, so if you try

          MM/DD/YYYY format first, 12/01/2015 will match this format and will be interpreted as Dec 1st.

           

          If you know the format per file, use an explicite date format parsing per file instead.

          • Re: Different date formats from different source files
            Stefan Wühl
            DATA.tmp:
            LOAD Date#(Date,'DD/MM/YYYY') as Date
            FROM
            [Data\Dates1.txt]
            (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
            
            
            LOAD Date#(Date,'MM/DD/YYYY') as Date
            FROM
            [Data\Dates2.txt]
            (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
            //
            //DATA_WITH_ALT:
            //NoConcatenate LOAD
            // Date(ALT(Date#(Date,'MM/DD/YYYY'),Date#(Date,'DD/MM/YYYY'),'No valid date')) as Date_ALT
            //RESIDENT DATA.tmp;
            //
            //DROP Table DATA.tmp;