topic Re: highest value with calculated dimension and suppress null in QlikView

highest value with calculated dimension and suppress null

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

Hello all

I have a (straight) table with rides delivering packages and go from one City Hub to another. Each ride is devided in parts. At the end of the ride is the part of the ride that goes from a hub to the receiver: no City_to is filled. See first table below.

City_to can be forgotten and the date can be false (manually filled).

I want to have a straight table with one row of each ride, with a field that contains the latest date of the parts that have no City_to. (something like the 2nd table below)

Just choosing the youngest date is not enough: ride 1 would go wrong as obviously 4-8-2016 is a wrong date.

If two date are the same (e.g. ride 3), one of them is to be picked

Re: highest value with calculated dimension and suppress null

agomes1971 — Fri, 02 Sep 2016 11:15:17 GMT

Hi,

please see the attached.

Regards

André Gomes

Re: highest value with calculated dimension and suppress null

sunny_talwar — Fri, 02 Sep 2016 11:26:04 GMT

I am not sure I understand why 4-8-2016 is a wrong date here? Can you elaborate?

Re: highest value with calculated dimension and suppress null

Gysbert_Wassenaar — Fri, 02 Sep 2016 11:37:54 GMT

Dimension: Ride

Expression1: FirstSortedValue({<City_to={''}>} aggr(maxstring(City),Date_Leaving,Ride), -aggr(Date_Leaving,Ride,Date_Leaving))

Expression2: max({<City_to={''}>}Date_Leaving)

Re: highest value with calculated dimension and suppress null

rajuamet — Fri, 02 Sep 2016 12:01:58 GMT

Hi J Harke,

Is this what u r expecting ? If yes, PFA.

Re: highest value with calculated dimension and suppress null

jharke — Mon, 05 Sep 2016 14:19:19 GMT

Hello André,

In your file, Ride 1 is connected with date 4-8-2016. But as I already stated in my question: this date is incorrect (filled manually by a driver and he made a mistake by filling this date). The row for Ride 1 should be with City C and Date 3-8-2016.

Re: highest value with calculated dimension and suppress null

jharke — Mon, 05 Sep 2016 14:43:12 GMT

Goodafternoon Rangaraju,

It seems to be working, but I can only 'design' not 'develop': I cannot change anything in the script, only in the front / visualization part. So I would like to have a solution at the side of the user interface.

Re: highest value with calculated dimension and suppress null

rajuamet — Tue, 06 Sep 2016 07:05:03 GMT

Hi J Harke,

solution at the side of user interface.

gysbert wassenaar's suggestion would do the trick, PFA.