Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Set Analysis - First Sorted Value inside the operator

Hi Everyone

I have made an example of what I am trying to do between 2 tables. Not sure what I'm doing wrong.

I am ultimately trying to retrieve the Txt, for the last available rule, per member. The tables were ordered in Load script.

The =FirstSortedValue(RuleID,-dteEffectiveDate) works fine and brings in the right ruleID.

Now to go a step further I want to bring in the text so I have tried using the relevant RuleID in the set analysis, with an aggregation and it doesn't seem to bring in the right text? I also changed the '-' and used Minstring etc but looks like it is something else I am missing here.

3.PNG1.PNG2.PNG

AGGR(
MaxString
(
{<RuleID = {"=FirstSortedValue(RuleID,-dteEffectiveDate)"}>}
Txt
)

,MemberID)

Labels (4)
2 Solutions

Accepted Solutions
Highlighted
MVP
MVP

May be simply:

=FirstSortedValue(Txt,-dteEffectiveDate)

View solution in original post

Highlighted
Specialist II
Specialist II

please check the attached

i have used similar exp : FirstSortedValue(Txt,-dteEffectiveDate)

View solution in original post

6 Replies
Highlighted
Partner
Partner

=MinString({<RuleID = {"=AGGR(FirstSortedValue(RuleID,-dteEffectiveDate),MemberID)"}>}Txt)
I also tried this, which consistently gives now the First Text, not the last.
Highlighted
Specialist II
Specialist II

Could you provide the sample input and desired output for better understanding and working scenario
Highlighted
Partner
Partner

Hi Anushree1

I will attach a QS app file with the example.

What I am trying to achieve is:

MemberRuleTxt
15bal
213ybndfv
320advrabbd

 

Rule is the "latest" applicable rule for the member. That calc is working. Txt which I cannot get right is the Txt column for the last rule for the member. 

I hope that helps

Highlighted
MVP
MVP

May be simply:

=FirstSortedValue(Txt,-dteEffectiveDate)

View solution in original post

Highlighted
Specialist II
Specialist II

please check the attached

i have used similar exp : FirstSortedValue(Txt,-dteEffectiveDate)

View solution in original post

Highlighted
Partner
Partner

Seems I over complicated it. Thank you very much.