Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
what i thought would be a straightforward piece of code, turns out not to be. No matter what formula i use on Qlik Load i cannot get the date from Excel to format correctly
In Excel the date field is formatted as custom i.e.
20/01/2024 13:30:00
20/01/2024 00:00:00
i am wanting just 20/01/2024 but to be recognised as date.
i've tried:
Date(Floor(TimeStamp#(IncidentDate, 'DD/MM/YYYY hh:mm:ss TT'))) as LC_DATE
date(IncidentDate,'DD/MM/YYYY') as LC_DATE
date(date#(IncidentDate,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as LC_DATE
it either comes back blank or on the surface looks as if it's worked, but showing as two lines i.e.
20/01/2024
20/01/2024
i'm sure there's someone really smart on this forum who can help me please 🙂
format should be
Date(floor(Date#([field_name], 'DD/MM/YYYY h:mm')),'YYYY-MM-DD') as NewDate;
still returning blank 😞
Try below
Date(floor(TIMESTAMP#([IncidentDate], 'YYYY-MM-DD hh:mm:ss')),'YYYY-MM-DD') as IncDateTest
again blank, it's bizarre, nothing seems to work
This is working for me
Data:
LOAD Distinct Date(floor(Date#([IncidentDate], 'DD/MM/YYYY hh:mm')),'DD/MM/YYYY') as NewDate;
LOAD * INLINE [
IncidentDate
20/01/2024 13:30
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
20/01/2024 00:00
];