Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
EmployeeID | AccessDate | =date([Access date],'d.m.yyyy') | month([Access date]) | right Access Date I want to see |
1 | 9.6.2020 | - | - | 9.6.2020 |
1 | 19.6.2020 | - | - | 19.6.2020 |
1 | 29.9.2020 | - | - | 29.9.2020 |
1 | 43995 | 13.0.2020 | June | 13.6.2020 |
1 | 43998 | 16.0.2020 | June | 16.6.2020 |
1 | 44003 | 21.0.2020 | June | 21.6.2020 |
1 | 44009 | 27.0.2020 | June | 27.6.2020 |
Please, any ideas where the problem is?
Thanks a lot!
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')))
Hi @ZuzJsk007 ,
Welcome to Qlik Sense.
Try this:
Date([Access date],'d.M.yyyy')
M must be in In capital letters 😁
Best,
Lotfi
Thank you vor your suggestion 🙂 Unfortunately, it doesn't help with everything 😕 I still see the column divided to NULLs and dates 😞
EmployeeID | AccessDate | =date([Access date],'d.m.yyyy') | month([Access date]) | right Access Date I want to see |
1 | 9.6.2020 | - | - | 9.6.2020 |
1 | 19.6.2020 | - | - | 19.6.2020 |
1 | 29.9.2020 | - | - | 29.9.2020 |
1 | 43995 | 13.6.2020 | June | 13.6.2020 |
1 | 43998 | 16.6.2020 | June | 16.6.2020 |
1 | 44003 | 21.6.2020 | June | 21.6.2020 |
1 | 44009 | 27.6.2020 | June | 27.6.2020 |
Hi
try this
date(date#([Access date],'D.M.YYYY'))
hope it helps
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')))
Thank you, the different formats in date column were exactly the problem!