Solved: Re: Displaying next sorted value where field is no... - Qlik Community

FakeJupiter · ‎2019-12-03

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.

veidlburkhard · ‎2019-12-03

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

View solution in original post

FakeJupiter · ‎2019-12-05

Here's the final solution I used. Basically you can get the idea from the script.

I made a Mapping Load that looked like this. I set it to load entries where Category is >2 so that some categories with 1 or 2 spaces do not load.

TamesTemp:
Mapping LOAD
Name,
Max(Date) as Max_Datums

Resident tmpTamesT where len(Category)>2
Group by Name;

Made a new data table.

LastSegment:
NoConcatenate
LOAD Category,
	Date(ApplyMap('TamesTemp',Name,0)) as Max_Datums,
	Date - ApplyMap('TamesTemp',Name,0) as Filter_Value,
	Name,  
	Date
   	
Resident tmpTamesT;

Made another mapping load.

LastSegment2:
Mapping LOAD Name,
	Category
	
Resident LastSegment where Filter_Value = 0;
Drop table LastSegment;

Made a final data table, adding field "Last_Segment" which shows the last available Category by date.

ApplyMap('LastSegment2',Name,'nav segmenta') as Last_Segment

Then I used Last_Segment field in my chart. Hope somebody can make a use of this.

View solution in original post

Arthur_Fong · ‎2019-12-03

Try this:

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

In AddOns > Data Handling > uncheck include zero values.

Arthur_Fong · ‎2019-12-03

Sorry did that on QS.

In QV, presentation > suppress null values.

FakeJupiter · ‎2019-12-03

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.

Arthur_Fong · ‎2019-12-03

Did you apply this expression?

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

I am working fine without null values displayed:

FakeJupiter · ‎2019-12-03

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.

Arthur_Fong · ‎2019-12-03

Can you screenshot your dimensions and expressions tab?

FakeJupiter · ‎2019-12-03

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:

FakeJupiter · ‎2019-12-03

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.

Arthur_Fong · ‎2019-12-03

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

Displaying next sorted value where field is not empty

FirstSortedValue

functions

Script

straight table