topic Re: Date List Box is not showing unique Value in QlikView

Date List Box is not showing unique Value

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a field called Ship Date with YYYY-MM-DD hh:mm:ss ffffff format. I am creating another field called ShipDate1 using below syntax.

Date(ShipDate,'MM/DD/YYYY') as ShipDate1

When I am putting this into List Box, the value is repeating. Could you please check once.

Thanks,

Sarif

Re: Date List Box is not showing unique Value

tresB — Wed, 26 Sep 2018 09:25:03 GMT

A small correction in script could help you. Try like:

Date(Floor(ShipDate),'MM/DD/YYYY') as ShipDate1,

Re: Date List Box is not showing unique Value

Anonymous — Wed, 26 Sep 2018 09:29:08 GMT

define an separate calendar (best would be a master calendar)

in a simple way:

Calendar:

load distinct

Date(ShipDate,'MM/DD/YYYY') as ShipDate1

resident FACT_PROOFOFDELIVERY

you may enhance the calender with year, month etc. look for mastercalender

above example should be sufficient for your requirment

and do not crreate the field ShipDate1 in FACT_PROOFOFDELIVERY

Re: Date List Box is not showing unique Value

mhmmd_srf — Wed, 26 Sep 2018 09:35:56 GMT

Hi TB,

How r u?

It is working now.

But I feel my code should also work. Any reason that my piece of code is not working?

Thanks,

Sarif

Re: Date List Box is not showing unique Value

tresB — Wed, 26 Sep 2018 09:51:00 GMT

Mohammad Sarif wrote:

But I feel my code should also work. Any reason that my piece of code is not working?

Unfortunately NOT. Your code should not work. And yes, it has reason. Date() is a formatting function, i.e. - it only changes the appearance of date values and doesn't make any change in the values at the back-end. Here you have to remember that dates are actually numbers at the back-end.

Having said that, since you are having multiple values for a date (with decimals), though you change the formatting the back-end numbers are still different. For example, say, you have two values 43369.10 (timestamp- 26/09/2018 02:24:00) and 43369.12 (timestamp - 26/09/2018 02:52:48). If you format them using date() you would see the date portion which is same (26/09/2018) for both, however, the back-end numbers are different (in decimals). Hence, qlik would show you the date portion only but twice because the values are different.

To get rid of this, you need to get the values same (not only the formats) and we use floor() for the same which actually changes the value to get the integer part.

Hope this helps. I know, I am not good at writing explanation.