How to handle date formatting of imported CSV file

[mikegrattan]

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:

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

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

Thank you.

[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.

[mikegrattan]

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:

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':

Thank you.

[#4 · Accepted solution]

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'))

[mikegrattan]

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.

[mikegrattan]

Modified IF statement to handle single-digit month and single-digit day:

    IF(Len("Date Received")=7,Date(Date#("Date Received",'MDDYYYY'),'MM/DD/YYYY'),

    IF(Len("Date Received")=6,Date(Date#("Date Received",'MDYYYY'),'MM/DD/YYYY'),

      Date(Date#("Date Received",'MMDDYYYY'),'MM/DD/YYYY'))) AS DateReceived,

Now I just need to be able to handle the case where the length of the date is 7, but the month could be single digit and the day double-digit, or the month could be double digit and the day is single digit.

[Nicole-Smith]

But if the date in your file is 1212016, how do you know if it is for January or December?  Is there something else in the file that confirms which month it is actually referring to?

[mikegrattan]

That's a problem that I might only be able to resolve by asking the lab to format the data with a leading zero for months or days that are single-digit.  There's no other way in the file to determine what the month actually is.

I think we've done all we can with the data we have to work with.

Thank you everybody for your contributions!