Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

Sample Data Based on Condition

Hi,

I have attached one sample file. From this file for each policies i want 5 such members which are having maximum Sum Assured.

Below given is the required out_put...

 

Policy_NoStatusMember_NoSumAssured
123456RD23457000
123456IF12457000
123456IF12356000
123456DH12232000
123456IF222231000
2365554RD32194000
2365554IF33390000
2365554RD12387000
2365554IF12487000
2365554DH22285000
146789IF101100000
146789IF104100000
146789IF10399999
146789IF10684756
146789IF10256897

Please help to get the desired result.

Thanks in Advance.

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

You could try this:

export:

load * where MaxFlag < 6;

load *, if(Policy_No = previous(Policy_No), peek('MaxFlag') + 1, 1) as MaxFlag

resident Source order by Policy_No, SumAssured desc;

store export into export.qvd (qvd);

- Marcus

View solution in original post

13 Replies
RonaldDoes
Partner
Partner

Max(SumAssured)?

Naamloos.png

pra_kale
Creator III
Creator III
Author

I want only 5 members from each policies which are having maximum sum assured.

RonaldDoes
Partner
Partner

Sorry, I misunderstood.

How about this: use

Aggr(Rank(Aggr(Sum(SumAssured), Policy_No, Member_No), Member_No), Policy_No, Member_No)

to find the rank of the member within its policy, and then using an if-statement, show just ranks 1-5 in your chart.

Naamloos.png

pra_kale
Creator III
Creator III
Author

Hi,

Thanks Ronald for your help..It is working.

Can you pl put the code in body as i can not open your application.

RonaldDoes
Partner
Partner

=If(

Aggr(Rank(Aggr(Sum(SumAssured), Policy_No, Member_No), Member_No), Policy_No, Member_No)>5,

Null(),

Aggr(Rank(Aggr(Sum(SumAssured), Policy_No, Member_No), Member_No), Policy_No, Member_No)

)

pra_kale
Creator III
Creator III
Author

Hi,

Thanks Ronald..

But, I am having a very large data set around 25 lacs. So whether it is possible we can do this at a script level. So at the end i will store data through STORE command.

Thanks again.

pra_kale
Creator III
Creator III
Author

Hello All,

Can you please help to resolve the issue..

Thanks in Advance.


swuehl

marcus_sommer

stalwar1

marcus_sommer
MVP & Luminary
MVP & Luminary

One way on script-level could be to use Peek() or Previous() ? in a sorted load to flag the records - maybe in this way:

load *, if(Policy_No = previous(Policy_No), peek('MaxFlag') + 1, 1) as MaxFlag

load *, if(Policy_No = previous(Policy_No), MaxFlag + 1, 1) as MaxFlag

resident Source order by Policy_No, SumAssured;

and within your dimensions/expressions you could use this flag like:

if(MaxFlag < 6, Policy_No, null()) // dimension

sum({< MaxFlag = {"<6"}>} SumAssured) // expression

- Marcus

pra_kale
Creator III
Creator III
Author

Hi,

Thanks Marcus for your very quick help...

But I am getting below given out-put after applying your condition..

                                                 

Policy_NoStatusMember_No=if(MaxFlag
  < 6, Policy_No, null())
MaxFlagSumAssured
2365554DH332236555418000
2365554DH226236555428000
2365554IF1202365554365132
2365554IF1002365554465132
2365554IF2202365554580000

Where as I want Top 5 members on the basis of Maximum Sum Assured per policy wise. Means Max flag should be on the basis of Sum Assured.

Eg. In the case of same policy I want below given out-put..

                                                 

Policy_NoSumAssuredMember_NoStatusMaxFlag
236555485000222DH6
236555487000123RD7
236555487000124IF8
236555490000333IF9
236555494000321RD10