topic Re: Displaying next sorted value where field is not empty in QlikView

Displaying next sorted value where field is not empty

FakeJupiter — Sat, 16 Nov 2024 01:48:04 GMT

Hello!

I have a table in my source file, say, like this (except not sorted by Date):

Name	Category	Date
John		07.07.2019
Anthony	A	06.06.2019
Chad	A	05.05.2019
Michael	C	04.04.2019
Hillel	B	03.03.2019
Josh	C	02.02.2019
John	D	01.01.2019
John	B	12.12.2018

What I want to achieve is to show the latest category in a chart (straight table) for every Name (which would be the dimension). I figured how to do this by using this expression:

=FirstSortedValue(Category,-Date)

The problem that I encountered is that, as I depicted in the table above, some names don't have respective categories in the latest (by Date) entry / entries. What I would like the table to show is the latest category that is not empty. In this example for John category D would be shown (showed?).

Any help would be much appreciated. Thanks!

EDIT:

We did not manage to find a solution how to achieve what I wanted in the chart settings without stepping into script. However, I got close. Here's a chart expression that worked for me in a simple sample file, but didn't for some reason work in my large and complex target file. In my target file it only returned null values. I figured it had something to do with the use of Aggr function.

=FirstSortedValue(Distinct if(isnull(Category)or len(Category)<1,null(),Category),-Aggr(Distinct Date, Category))
With the help of veidlburkhard I managed to develop a solution via script. I'll post it in this thread.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 08:33:10 GMT

Try this:

FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date)

In AddOns > Data Handling > uncheck include zero values.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 08:38:50 GMT

Sorry did that on QS.

In QV, presentation > suppress null values.

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 08:48:37 GMT

Thanks for the answer.

Is it really necessary to edit the AddOns settings to achieve this? I am developing this QV file for various people, and it has a lot of sheets, tables and charts. Would it be wise to change such a global setting?

I did, however, change the chart's settings in the Presentation tab to suppress zero values and used the script you provided. It didn't work unfortunately.

EDIT:

I tried doing that, it didn't work.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 08:52:43 GMT

Did you apply this expression?

FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date)

I am working fine without null values displayed:

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 08:53:57 GMT

Yes. It gives me the same result as the basic formula in my original post.

Here's my chart presentation settings just in case. The column "Servisa prioritāte" = "Category" in my example.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 08:55:51 GMT

Can you screenshot your dimensions and expressions tab?

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 10:22:53 GMT

Sure. In Dimensions "Serviss" equals "Name" in my example.
In Expressions "Servisa prioritāte" equals "Category" in my example.

I figured out that for your example to work, I must also:

Use a date dimension in my chart
I can't have another Expression [Lietu sk.] which counts how many cases (row entries) there are for each name (Serviss). The formula is a simple "=Count([Lietas Nr.])".

Is there any way I can do this with only the Serviss (Name) dimension and having the count expression as well?

Posting the screenshots below:

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 10:34:09 GMT

I'll try adding screenshots to this post, but my last reply with screenshots got marked as spam for some reason.

Basically, I found out that your solution works, but I have to add Date to dimensions and I can't have a count expression in addition to the FirstSortedValue expression that counts the total entries (rows) in the data set.

Is there any way to achieve the same result, but without having to add Date dimension and having the additional count expression?

Serviss = Name
Servisa prioritāte = Category

In your solution also there will be several rows for the Name. However, what I would like to, is to have only one row for each name and for it to show the latest (by date) Category available, meaning the latest category entry for the Name that is not zero or empty.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 12:19:32 GMT

Instead of adding the category as expression,

Create a calculated dimension and try this:

=aggr(FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date),Date)

Suppress null value at presentation layer.

Thanks and regards,

Arthur Fong

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 12:52:28 GMT

This doesn't really work for me, because I need to only have each name appear once. In your example John appears twice. Basically, what I need is a list of all names (unique) and their most recent category, which is not empty, but an actual category.

In the actual file I'm developing, I seek to show the list of all car services and the category they belong to. This category can change over time; therefore, I need to show the latest category. However, in some cases the latest entry / entries for the car service have category field missing, so I at least would like to assign the latest available category the car service had in the previous records (lines).

Thank you for your answer.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 13:08:20 GMT

Try this in calculated dimension:

=aggr(FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date),Name)

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 13:28:05 GMT

Still doesn't work.

I made a simple load for the example as follows:

Data: Load * inline [ Name|Category|Date John||06.06.2019 Chad|B|05.05.2019 John|C|04.04.2019 John|D|03.04.2019 Chad||01.01.2019 Chad|U|29.12.2018 ] (delimiter is '|');

The result is as follows:

If I check "Suppress when value is null" at the Dimension tab for the calculated dimension, "John" disappears.
At Presentation tab all "Suppres Zero-Values" are checked.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 13:57:15 GMT

Try this at calculated dimension:

=aggr(FirstSortedValue(Category,if(ISNULL(Category)or len(Category)<1,null(),-Date)),Name)

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 14:43:43 GMT

This seems to work in the example load and table, but for some reason doesn't work in the actual file I need it to work in.

I checked all "Suppress Zero-Value" boxes, they all match.

I don't know what else to look for. In the main file, I also use 2 dimensions - Serviss (analogue to Name) and the calculated dimension that you posted. Naturally, I replaced all the field names as necessary. I have only 1 Expression that is "=count([Lietas Nr.])" which basically is the same thing as "=count(Date)". Here is my version of the calculated dimension with the appropriate field names for the main file:

=aggr(FirstSortedValue([Servisa prioritāte],if(ISNULL([Servisa prioritāte])or len([Servisa prioritāte])<1,null(),-[Saskaņošanas datums])),Serviss)

In case you might find it of any use, I posted the tables from the main file below. On the left I have the table where I used the simple expression from my original post to show category ("Servisa prioritāte"), and on the right I have the table that uses the calculated dimension you provided. Obviously, the tables look the same, unfortunately.

EDIT: What I just noticed is that in some cases it actually does work. In the table above to the right one row has what I need in the category (Servisa prioritāte) field. I can't figure out why it doesn't work in other cases though.

Re: Displaying next sorted value where field is not empty

Arthur_Fong — Tue, 03 Dec 2019 14:48:41 GMT

Check if your date is text or numeric format.

Create a listbox and screenshot your date column.

Re: Displaying next sorted value where field is not empty

veidlburkhard — Tue, 03 Dec 2019 14:49:15 GMT

Hi Hoodoo,

why not doing it simple like this:

If(IsNull(Category),FirstSortedValue(Category,-Date, 2), FirstSortedValue(Category,-Date))

and Count(Name)

See the picture:

Happy qliking

Burkhard

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 15:08:06 GMT

All dates seem to be in date format. I checked through the whole list box and the source file.

I have a suspicion that it might have something to do with the data from the source file. In one of the cases where your provided calculated dimension doesn't work, it, as well as the short formula from my original post should work, as the source file did not have latest by date category field empty. So why does it return a "-"?

Re: Displaying next sorted value where field is not empty

FakeJupiter — Tue, 03 Dec 2019 15:30:36 GMT

Hi!

Thanks for your answer.

I encountered two problems with this approach.

One is that, if I understood the function properly, it wouldn't work if the second newest (by Date) Category would also be empty. In my real case not example I have some cases where in the source file there are, for instance, a total of 500 rows with the same Name, but the latest 50 don't have a category.

The second is that for some reason it works on the sample table I provided in my original post, but doesn't work on the example I provided afterwards.

When I have load as follows:

Data: Load * inline [ Name|Category|Date John||06.06.2019 Chad|B|05.05.2019 John|C|04.04.2019 John|D|03.04.2019 Chad||01.01.2019 Chad|U|29.12.2018 ] (delimiter is '|');

it returns this:

Re: Displaying next sorted value where field is not empty

veidlburkhard — Tue, 03 Dec 2019 17:14:38 GMT

Hi Hoodoo,

if this is so, you have to calculate a Category Flag in your script in order to identify the empty categories:

LOAD
Name,
Category,
If(IsNull(Category) or Len(Category) = 0, 0, 1) as FlagCat,
Date;
Load * inline [
Name|Category|Date
John||06.06.2019
Chad|B|05.05.2019
John|C|04.04.2019
John|D|03.04.2019
Chad||01.01.2019
Chad|U|29.12.2018
] (delimiter is '|');

and use this flag in the set analysis of your expression.

See the picture:

Hope this solves your problems.

Best regards

Burkhard