5 Replies Latest reply: Dec 9, 2016 2:20 PM by Markian Zadony RSS

    Add Data imports dates incorrectly...

    Markian Zadony

      Hi...

       

      I'm very new to Qlik Sense, testing it out to see if it would meet our needs--visualizing raw data files before they get loaded into our DW.  I have been banging my head trying to figure out why when I "Add Data" (via Data Manager) to an already existing table, all date fields in Qlik Sense are messed up in the 2nd file.

       

      So, I have 2 incoming files, same schema in both (pipe delimited w/header row).  Dates come in as YYYYMMDD.  Qlik Sense imports the first file correctly and sets the dates accordingly.  When I "Add Data" & upload the 2nd file, all the dates import incorrectly, creating 5-digit or 6-digit years...

       

      Each file contains a "run date" column, which contains the same date for every row in that file.  So, here's how they import...

      • File1 run date value: '20161124' becomes '11/24/2016' (good)
      • Add Data: File2 run date value: '20161117' becomes '3/29/57099' (bad)

       

      This problem is across the board for all date fields in File2 (which all use YYYYMMDD), and I couldn't find anyone else experiencing a similar problem...

       

      Thanks!

        • Re: Add Data imports dates incorrectly...
          Robert Mika

          try:

          DATE(DATE#(yourfield,'YYYYMMDD'),'MM/DD/YYYY') AS Date

            • Re: Add Data imports dates incorrectly...
              Markian Zadony

              Hi Robert...

               

              I'm a little confused where I should do this.  The original import read File1 correctly as YYYYMMDD, but Add Data for File2 didn't.  (Did it interpret those as 20161117 days past 1/1/1900?)  Therefore, my end result is this mix of '11/24/2016' (File1) and '3/29/57099' (File2) for this field.

               

              So, are you saying this code above should be added to the import, but only for File2? ***  Or some other place (e.g. a funcion on a field in a chart/table)--therefore changing the date on the fly?  If it's the latter, how do I apply the code only for those values that are problematic?

               

              *** I'm curious to know if there's an inconsistency between how dates are determined in Qlik Sense of initial imports vs. Add Data?

                • Re: Add Data imports dates incorrectly...
                  Robert Mika

                  Are both of those files coming from the same source?

                  You need to add this part at the script level:

                   

                  LOAD

                  Field1,

                  DATE(DATE#(yourfield,'YYYYMMDD'),'MM/DD/YYYY') AS Date

                   

                  FROM...

                   

                   

                  If only File2causee the problem add only into this File.

                    • Re: Add Data imports dates incorrectly...
                      Markian Zadony

                      So, just so I understand...  When I bring it in via Add Data (using the wizard-like interface, set the delimiter, then "Load data and finish"), Qlik Sense properly sees the YYYYMMDD fields in the 1st file properly, and sets them as dates.

                       

                      However, despite having an identical schema (including the same delimiter, field names, field order, and YYYYMMDD date format), I need to use a script to append the 2nd file into the dataset--manually setting each YYYYMMDD date field, in order to make sure that 2nd file gets loaded in the same way?

                       

                      Seems like a bug to me in Add Data, where the code sees dates the first time around yet sees them as integers to be added to a date like 1/1/1900 on subsequent data appends (despite the target field already existing as a date)...

                • Re: Add Data imports dates incorrectly...
                  Vladimir Komarov

                  I would not trust Qlik to convert YYYYMMDD string to a Date format directly.

                  I am usually splitting the string into YYYY, MM, and DD substrings and building a date field using MakeDate() function:

                   

                  MakeDate(num#(left("TXN_DT", 4)), num#(mid("TXN_DT", 5, 2)), num#(right("TXN_DT", 2))) as [Trans Date]

                   

                  Works every time!

                   

                  Regards,

                  Vlad