Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZuzJsk007
Contributor III
Contributor III

mixed date format in one column

Hi everybody, I'm new to Qlik and I stumbled upon an interesting thing:

I have data in xlsx format, various tables, various datatypes, all O.K. loaded into Qlik except one column "Access date". In Excel, it looks like a normal datum. But when I create a table in Qlik, it looks like table below. I tried formating via date() in the Table (see below). I tried formatting also in load (date() and date#())  but with no effect.

EmployeeIDAccessDate=date([Access date],'d.m.yyyy')month([Access date])right Access Date I want to see
19.6.2020--9.6.2020
119.6.2020--19.6.2020
129.9.2020--29.9.2020
14399513.0.2020June13.6.2020
14399816.0.2020June16.6.2020
14400321.0.2020June21.6.2020
14400927.0.2020June27.6.2020

 

Please, any ideas where the problem is?

Thanks a lot!

Labels (1)
1 Solution

Accepted Solutions
arulsettu
Master III
Master III

Try this for different date formats

load * Inline [
date
43995
21.9.2020
];

date(alt(date#(date,'D.M.YYYY'),date(date,'D.M.YYYY')))

View solution in original post

5 Replies
lotfiblo
Creator
Creator

Hi @ZuzJsk007 ,

Welcome to Qlik Sense.

Try this:

Date([Access date],'d.M.yyyy')

M must be in In capital letters 😁

Best,

Lotfi

Use Qalyptus for your Qlik Sense on-premise and Qlik Sense SaaS Reporting
Connect with me on Linkedin
ZuzJsk007
Contributor III
Contributor III
Author

Thank you vor your suggestion 🙂 Unfortunately, it doesn't help with everything 😕 I still see the column divided to NULLs and dates 😞

 

EmployeeIDAccessDate=date([Access date],'d.m.yyyy')month([Access date])right Access Date I want to see
19.6.2020--9.6.2020
119.6.2020--19.6.2020
129.9.2020--29.9.2020
14399513.6.2020June13.6.2020
14399816.6.2020June16.6.2020
14400321.6.2020June21.6.2020
14400927.6.2020June27.6.2020

 

brunobertels
Master
Master

Hi  

try this 

date(date#([Access date],'D.M.YYYY'))

hope it helps

 

 

arulsettu
Master III
Master III

Try this for different date formats

load * Inline [
date
43995
21.9.2020
];

date(alt(date#(date,'D.M.YYYY'),date(date,'D.M.YYYY')))

ZuzJsk007
Contributor III
Contributor III
Author

Thank you, the different formats in date column were exactly the problem!