Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have timestamp field "Purchased Time" in my orders table, say if there are multiple orders purchased on the same day there timestamp will be recorded. Now i am looking to generate a calendar with this field. I use Date("Purchased Time",'M/D/YYYY') as PurchasedDate in getting other calendar related entries like Day,Week etc., Now PurchasedDate is containing duplicates since many orders purchased on the same day will have entries, how to remove the duplicates and when i select one particular day say 8/26/2012 it should select all the orders purchased on that day irrespective of the timestamp.
Cheers,
Srikar
Srikar
The Date() function does not truncate the date (to remove the time portion). It is a display format function. So although the dates now look like plain dates, the underlying value contains the time portion. These then look like duplicates in a list box.
To remove the time portion, use floor(), like this
Date(Floor([Purchased Time]), 'M/D/YYYY') As PurchasedDate
Now you should get one instance of each date in the list box
Hope that helps
Jonathan
I think your duplicates are because Qlikview stores dates in dual. So a datetime is a number like 40403 and a datetime may be
40403,1231511
and a few minutes later on this day, the datetime may be
40403,1232511
So if you want to see only the dates (and want to disregard time...) then you need to use floor() to only keep the 40403 part...
See an example attached, where initially you see duplicates in the selectbox (due to not using floor() ).
When you change the loadscript (comment the current script and uncomment the script that I prepared for you) then the listbox should not contain duplicates anymore.
I hope it helps.