Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone!
I am getting data onto Qlikview from excel. In it is a column for date, but its formatted in a way that both date and time are present in the cell, for example:
2014-01-19 23:59:33.0
Now, from it I only need date, not the time. I tried using the date function, but when i use it as a list box, the same date turns up multiple times( as many times as its entries in the excel)
I need the date as unique values in a list, which function can i use?
Thanks in advance for all the queries.
Hi Jonathan,
Instead of date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD hh:mm:ss.ff')))
can I use
date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD')))
so i get only the date and not the timestamp?
LOAD Date(Floor([Request Time])) as Date
FROM
.....\sample data for date.xlsx]
(ooxml, embedded labels, table is Sheet1);
Hi Jonathan,
I just read what i replied to you and realized it was pretty stupid.
I'll try your method.
P Sushant wrote:
Instead of date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD hh:mm:ss.ff')))
can I use
date(floor(Date#('2014-01-19 23:59:33.0', 'YYYY-MM-DD')))
so i get only the date and not the timestamp?
No - the date# must match the format string. The floor() removes the time portion.
date#() - recognise the string as a date and convert it
floor() discard the fractional portion leaving integer days
date() format the output from floor using the default date format in your model
HTH
Jonathan
Ladies and gentlemen , we have a WINNER!!!!!
Tresesco is the one who tackled the problem and WON! Thanks a LOT!!!!
Thank you Everyone for all the help, you guys are awesome!!!!