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.

[marcus_sommer]

Try it with changing the sort-order:

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

resident Source order by Policy_No, SumAssured desc;

- Marcus

[pra_kale]

Hi,

Marcus You are too good...It is working fine.

Just want to ask one thing whether it is possible to do this completely at back-end. Means without creating Calculated Dimension and expression. So at end of the script we will just use STORE command and required data get's stored.

Thanks Once Again for your Help...

[marcus_sommer · Accepted solution]

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

[pra_kale]

Thanks Marcus...It is working fine.. Great.