Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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)
2 Solutions

Accepted Solutions
veidlburkhard
Creator III
Creator III

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:

Test_FirstsortedValue.jpg

Hope this solves your problems.

Best regards

Burkhard

 

View solution in original post

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.

View solution in original post

26 Replies
Arthur_Fong
Partner
Partner

Try this:

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

In AddOns > Data Handling > uncheck include zero values.

Arthur_Fong
Partner
Partner

Sorry did that on QS.

In QV, presentation > suppress null values.

FakeJupiter
Creator
Creator
Author

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
Partner
Partner

Did you apply this expression?

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

I am working fine without null values displayed:

MC.PNG

FakeJupiter
Creator
Creator
Author

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.

image.png

Arthur_Fong
Partner
Partner

Can you screenshot your dimensions and expressions tab?

FakeJupiter
Creator
Creator
Author

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:

  1. Use a date dimension in my chart
  2. 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
Creator
Creator
Author

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.

image.pngimage.png

Arthur_Fong
Partner
Partner

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.

MC.PNG

Thanks and regards,

Arthur Fong