Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FakeJupiter
Creator
Creator

Displaying next sorted value where field is not empty

Hello!

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

NameCategoryDate
John 07.07.2019
AnthonyA06.06.2019
ChadA05.05.2019
MichaelC04.04.2019
HillelB03.03.2019
JoshC02.02.2019
JohnD01.01.2019
JohnB12.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:

  1. 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))
  2. With the help of veidlburkhard I managed to develop a solution via script. I'll post it in this thread.
Labels (4)
26 Replies
FakeJupiter
Creator
Creator
Author

Thank you.

I'm sure this would work. Using this approach, is there any chance I could make a straight table, filtering out some of the categories, so that only, say, categories "A"and "C" would show up in the table and the names not belonging to said categories would not be included in the straight table?

veidlburkhard
Creator III
Creator III

Yes, Hoodoo, this filtering will work, see the picture:

 

Test_FirstsortedValue.jpg

Best regards

Burkhard

FakeJupiter
Creator
Creator
Author

Sorry for not specifying, but is there a way to achieve that without making a selection / filter? Like you would when using a set analysis. My goal would be for the table to only show the list of the Names with certain categories without having to manually select them in filter.

veidlburkhard
Creator III
Creator III

this is easy to manage with set analysis, Hoodoo.

See the picture (only category B is shown, if you want to show several categories,

use a list instead, e.g. category = {'A', 'D', 'F'}):

Test_FirstsortedValue.jpg

FakeJupiter
Creator
Creator
Author

I think I figured out one of the problems with using this chart expression that you provided:

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

In some cases it doesn't work, because the most recent date is not unique, and the function doesn't know which value to choose; therefore, it returns null.

Here's what worked for me in the example file:

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

However, it again does not work in the main file. I get null values next to every name (Serviss). It has something to do with the Aggr / Date part of the expression, I'm sure.

FakeJupiter
Creator
Creator
Author

I made my a custom solution using a similar approach to what you suggested. I'll post it in this thread as well, if you are interested.

Anyways, thanks for your answer!

FakeJupiter
Creator
Creator
Author

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.