topic Re: Latest newest distinct value in text object in QlikView

Latest newest distinct value in text object

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to show the latest newest distinct value in a text object based on the date. So:

01-04-2017 Apple

01-04-2017 Strawberry

02-04-2017 Strawberry

02-04-2017 Apple

04-04-2017 Banana

05-04-2017 Apple

05-04-2017 Strawberry

The result in the text object must be Banana, as it is the latest new distinct value based on the data. How can I solve it?

Thanks!

Re: Latest newest distinct value in text object

sunny_talwar — Fri, 07 Apr 2017 18:04:27 GMT

May be like this

FirstSortedValue({<Fruit = {"=Count(DISTINCT Date) = 1"}>}Fruit, -Date)

Re: Latest newest distinct value in text object

Fri, 07 Apr 2017 18:11:55 GMT

Thank you! It works!

Re: Latest newest distinct value in text object

Sat, 08 Apr 2017 10:07:14 GMT

I see above solution is working only if there is one banana. But suppose there are multiple Banana's, but I still want to get a solution banana as it was the latest new distinct value:

01-04-2017 Apple

01-04-2017 Strawberry

02-04-2017 Strawberry

02-04-2017 Apple

04-04-2017 Banana

05-04-2017 Apple

05-04-2017 Strawberry

05-04-2017 Banana

I expect a textbox with the value Banana, as it was the latest original value based on the date.

Re: Latest newest distinct value in text object

sunny_talwar — Sun, 09 Apr 2017 16:18:56 GMT

May be this

=FirstSortedValue({<Date = {"=Count(Product) = 1"}>}Product, Date)

Re: Latest newest distinct value in text object

Tue, 11 Apr 2017 16:45:14 GMT

Thanks Sunny, it works! Only in case that on the same date there are two unique new values it goes wrong. I tried to change Date by something like a RowNo or with Time, but I did not make it. Any suggestions?

RowNo, Date, Time, Product

1, 01-04-2017, 20:15, Apple

2, 01-04-2017, 20:45, Strawberry

3, 02-04-2017, 18:00, Strawberry

4, 02-04-2017, 19:30, Apple

5, 04-04-2017, 20:10, Apple

6, 05-04-2017, 15:45, Apple

7, 05-04-2017, 17:20, Strawberry

8, 06-04-2017, 19:25, Banana

9, 06-04-2017, 20:20, Orange

In this case I expect Orange, but I am getting '-' (which I do understand why that happens).

Re: Latest newest distinct value in text object

sunny_talwar — Tue, 11 Apr 2017 17:03:15 GMT

Can you re-explain the logic behind picking Orange or Banana in the previous examples.... I am not sure I understand the logic (after few of our iterations)

Re: Latest newest distinct value in text object

Tue, 11 Apr 2017 17:13:35 GMT

Ofcourse, what I want is to see the newest distinct value. You can see it as arrivals of products and I want to know which is the newest product which is received for the first time. In the last case I posted, you can see that on 06-04-2017 I received Banana and Orange. I received both products for the first time, but I received Oranges about a hour later than the Banana so the Orange is the latest product I received for the first time.

You can see it also like:

1, 01-04-2017, 20:15, Apple (first receive of Apples)

2, 01-04-2017, 20:45, Strawberry(first receive of Strawberry)

3, 02-04-2017, 18:00, Strawberry (Second receive of Strawberry)

4, 02-04-2017, 19:30, Apple (Second receive of Apples)

5, 04-04-2017, 20:10, Apple (Third receive of Apples)

6, 05-04-2017, 15:45, Apple (Fourth receive of Apples)

7, 05-04-2017, 17:20, Strawberry (Third receive of Strawberry)

8, 06-04-2017, 19:25, Banana (First receive of Banana)

9, 06-04-2017, 20:20, Orange (First receive of Orange)

I am always looking for the latest first receive in my list. So even in the case that on 07-04-2017 I do receive again Oranges, I still expect that my output in a text object is Orange as it is (based on date and time) my latest first receive, even if I received it on 07-04-2017 for the second time. I can imagine it is a difficult case to understand, sorry for that.