Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Sample Data Based on Condition

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

Re: Sample Data Based on Condition

Max(SumAssured)?

Naamloos.png

Highlighted
Creator III
Creator III

Re: Sample Data Based on Condition

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

Highlighted
Partner
Partner

Re: Sample Data Based on Condition

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

Highlighted
Creator III
Creator III

Re: Sample Data Based on Condition

Hi,

Thanks Ronald for your help..It is working.

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

Highlighted
Partner
Partner

Re: Sample Data Based on Condition

=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)

)

Highlighted
Creator III
Creator III

Re: Sample Data Based on Condition

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.

Highlighted
Creator III
Creator III

Re: Sample Data Based on Condition

Hello All,

Can you please help to resolve the issue..

Thanks in Advance.


swuehl

marcus_sommer

stalwar1

Highlighted
MVP & Luminary
MVP & Luminary

Re: Sample Data Based on Condition

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

Highlighted
Creator III
Creator III

Re: Sample Data Based on Condition

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