Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

How to handle date formatting of imported CSV file

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.

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

7 Replies
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
Creator III
Creator III
Author

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.

Not applicable

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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!