topic Re: Calendar over timestamp field without duplicates. in QlikView

Calendar over timestamp field without duplicates.

Mon, 27 Aug 2012 07:36:55 GMT

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

Re: Calendar over timestamp field without duplicates.

jonathandienst — Mon, 27 Aug 2012 08:06:35 GMT

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

Re: Calendar over timestamp field without duplicates.

Mon, 27 Aug 2012 08:06:36 GMT

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.