7 Replies Latest reply: Jun 9, 2016 10:21 AM by Mike Grattan RSS

    How to handle date formatting of imported CSV file

    Mike Grattan

      I am receiving a CSV file from an outside service.  The date fields are in the following format:

       

      5122016

      5132016

      etc.

       

      I don't have any historical data yet, as this is a new service.  However, since the data I've received so far indicates that there will single digit and double digit days and months (no leading zeroes), I'm having a difficult time coming up with a way to import this data with properly formatted dates.  I've tried the Date() function and the Date#() function, but the results are incorrect.  The following screen shot shows the native data after it's imported to Qlik Sense:

      LabData1.jpg

      Here's another screen shot showing the result of using the Date() function:

      LabData2.jpg

      Any suggestions on how to handle this date format would be appreciated.

       

      Thank you.

        • Re: How to handle date formatting of imported CSV file
          Nicole Smith

          Have you tried this?

           

          Date#(DateReceived, 'MDYYYY')

           

          Explanation: The Date#() function tells QlikView that it is a date and how it is formatted.

            • Re: How to handle date formatting of imported CSV file
              Mike Grattan

              Thank you for the suggestion, but when I tried that Date# function, per your example, the resulting values were identical to the original values.

               

              I took your suggestion a step further, and wrapped it in the Date() function as follows:

               

              Date(Date#("Date Received",'MDDYYYY'),'M/DD/YYYY') as DateReceived,

               

              This resulted in the following:

              LabData3.jpg

              So, as you can see, the date looks good now.  However, I anticipate there could be a problem when the number of digits in the month is 2 instead of 1, or the number of digits in the day is 1 instead of 2.   Here's what happens if I change it to 'M/D/YYYY':

               

              LabData4.jpg

               

               

              Thank you.

            • Re: How to handle date formatting of imported CSV file
              Nhu Ngo

              Hi,

               

              you can try this.

               

              As Nicole mentioned, the Date# tells how the original date is formatted, because it doesn't always in this formal DDMMYYYY, so we might try with 2 different formats for each case

               

              Then the Date function format that date to the way you like

               

              IF(Len(DateReceived)=7,Date(Date#(DateReceived,'DMMYYYY'),'DD/MM/YYYY'),

                   Date(Date#(DateReceived,'DDMMYYYY'),'DD/MM/YYYY'))

                • Re: How to handle date formatting of imported CSV file
                  Mike Grattan

                  Nhu,

                   

                  That suggestion is certainly getting me closer to a solution.  However, it seems that more scenarios need to be considered, such as when the month has two digits and the day has one digit, the month as one digit and the day has one digit.  I changed the order to Month/Day/Year, so the final format would be MM/DD/YYYY for US formatting.  However, when the date is, for example, December 1, 2016, it will show up in the CSV file as 1212016, and the final result from the load script would be 1/21/2016.