topic Re: Sample Data Based on Condition in QlikView

Sample Data Based on Condition

pra_kale — Tue, 30 May 2017 13:16:25 GMT

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.

Re: Sample Data Based on Condition

RonaldDoes — Tue, 30 May 2017 13:27:22 GMT

Max(SumAssured)?

Re: Sample Data Based on Condition

pra_kale — Tue, 30 May 2017 13:41:43 GMT

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

Re: Sample Data Based on Condition

RonaldDoes — Tue, 30 May 2017 14:15:47 GMT

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.

Re: Sample Data Based on Condition

pra_kale — Tue, 30 May 2017 14:35:45 GMT

Hi,

Thanks Ronald for your help..It is working.

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

Re: Sample Data Based on Condition

RonaldDoes — Tue, 30 May 2017 14:54:10 GMT

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

Re: Sample Data Based on Condition

pra_kale — Tue, 30 May 2017 15:31:21 GMT

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.

Re: Sample Data Based on Condition

pra_kale — Wed, 31 May 2017 04:58:34 GMT

Hello All,

Can you please help to resolve the issue..

Thanks in Advance.

swuehl‌

marcus_sommer‌

stalwar1‌

Re: Sample Data Based on Condition

marcus_sommer — Wed, 31 May 2017 05:12:51 GMT

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

Re: Sample Data Based on Condition

pra_kale — Wed, 31 May 2017 05:49:30 GMT

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

Re: Sample Data Based on Condition

marcus_sommer — Wed, 31 May 2017 06:27:04 GMT

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

Re: Sample Data Based on Condition

pra_kale — Wed, 31 May 2017 09:04:45 GMT

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...

Re: Sample Data Based on Condition

marcus_sommer — Wed, 31 May 2017 10:10:27 GMT

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

Re: Sample Data Based on Condition

pra_kale — Wed, 31 May 2017 12:55:08 GMT

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