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.
LOAD Date(Floor([Request Time])) as Date
FROM
.....\sample data for date.xlsx]
(ooxml, embedded labels, table is Sheet1);
try this
date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'DD-MM-YYYYY')
Hi,
so you want only the date in QlikView, but in your base_data you have multiple entries per date?
That cannot be done easily - well, you could cut off the time_part using a STRING function, but that would cause you to lose data, wouldn't it?
=> If you don't need multiple records per day, but only one, try using FIRST() or SUBSTRING().
Trying now. Will keep you posted.
Hi Datanibbler,
There is only loss of the time part of the data, there are not multiple dates in each cell, just a date and a time.
Hi,
You can use these;
subfield(F1,' ',1) as date1,
or
date#(left(F1,10),'YYYY-MM-DD') as date
HTH
-Shruti
Hi..
first of all set the environment variable to the desired format of date
SET DateFormat='MM/DD/YYYY';
after that you can try this expression
date(date#(datefield,'YYYY-MM-DD hh:mm:ss'),'MM/DD/YYYY')
HTH
Sushil
Add Below Lines to your script, it will completely remove the time field....
Date(Floor(YourDateField,'YYYY-MM-DD')
Hi,
Try this,
Date(Floor(DateField),'MM/DD/YYY'Y)
Hope it helps you!!!
Thanks,
Jagan
Hi Sushil,
This returned a list box which was empty