Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Adil-Haider
Contributor III
Contributor III

Dealing with date formats in QlikSense

Hello Everyone,

I am loading data from excel and getting below result.  Can anyone please help here to convert it to date. Attaching the excel file as well.

 

Capture.JPG

Labels (1)
1 Solution

Accepted Solutions
Anil_Babu_Samineni

Looks like the null records are not reading as Date format. Perhaps this?

Alt(Date(Date#(TransactionDate, 'DD/MM/YYYY')), Date(TransactionDate)) as TransactionDate

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

5 Replies
Anil_Babu_Samineni

May be?

Date(Date#(TransactionDate,'DD/MM/YYYY'))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Adil-Haider
Contributor III
Contributor III
Author

It gives below result.

result.JPG

 

Anil_Babu_Samineni

Looks like the null records are not reading as Date format. Perhaps this?

Alt(Date(Date#(TransactionDate, 'DD/MM/YYYY')), Date(TransactionDate)) as TransactionDate

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Adil-Haider
Contributor III
Contributor III
Author

Thanks..it works!!!!
pradosh_thakur
Master II
Master II

I had a quick look at your data and added 1 to the next column and found the below. Your data seems to be in DD/MM/YYYY AND DD-MM-YYYY format.

calender.JPG

Try the below

Alt(Date(date#([Transaction Date],'DD/MM/YYYY'),'DD/MM/YYYY'),
Date(date#([Transaction Date],'DD-MM-YYYY'),'DD/MM/YYYY'))

OR

Date(date#(PURGECHAR(TEXT([Transaction Date]),'/-'),'DDMMYYYY'),'DD/MM/YYYY')
Learning never stops.