Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Max(SumAssured)?
I want only 5 members from each policies which are having maximum sum assured.
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.
Hi,
Thanks Ronald for your help..It is working.
Can you pl put the code in body as i can not open your application.
=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)
)
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.
Hello All,
Can you please help to resolve the issue..
Thanks in Advance.
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
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 |