Sample Data Based on Condition

[pra_kale]

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_No	Status	Member_No	SumAssured
123456	RD	234	57000
123456	IF	124	57000
123456	IF	123	56000
123456	DH	122	32000
123456	IF	2222	31000
2365554	RD	321	94000
2365554	IF	333	90000
2365554	RD	123	87000
2365554	IF	124	87000
2365554	DH	222	85000
146789	IF	101	100000
146789	IF	104	100000
146789	IF	103	99999
146789	IF	106	84756
146789	IF	102	56897

Please help to get the desired result.

Thanks in Advance.

[RonaldDoes]

Max(SumAssured)?

[pra_kale]

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

[RonaldDoes]

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.

[pra_kale]

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]

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

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]

Hello All,

Can you please help to resolve the issue..

Thanks in Advance.

swuehl‌

marcus_sommer‌

stalwar1‌

[marcus_sommer]

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]

Hi,

Thanks Marcus for your very quick help...

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

Policy_No	Status	Member_No	=if(MaxFlag   < 6, Policy_No, null())	MaxFlag	SumAssured
2365554	DH	332	2365554	1	8000
2365554	DH	226	2365554	2	8000
2365554	IF	120	2365554	3	65132
2365554	IF	100	2365554	4	65132
2365554	IF	220	2365554	5	80000

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_No	SumAssured	Member_No	Status	MaxFlag
2365554	85000	222	DH	6
2365554	87000	123	RD	7
2365554	87000	124	IF	8
2365554	90000	333	IF	9
2365554	94000	321	RD	10