topic Re: How To Aggregate the value under one in QlikView

How To Aggregate the value under one

pra_kale — Tue, 17 Jul 2018 18:08:09 GMT

Hi,

I have a data in which CHDRNUM, GROUP_NO, Product,STATUS and Premium columns are there. I want to aggregate the total of premium under one CHDRNUM having status IF, only if Group_No is repeated and any one policy is having status IF and only for product code PPT and GST. Thouh Group_No is repeated but non of the policy is having status IF then aggregation is not allowed.

For rest product codes only policy status IF and PU Should be get considered. No Aggregation. So my final out-put should look like this.

CHDRNNUM	GROUP_NO	Product	STATUS	Premium
2345678	803402	PPT	IF	3500
987456	8456123	GST	IF	5500
32456789	8999912	PST	IF	56000
98723	8912346	CNT	IF	300
6978	8912346	CNT	PU	4500

Your help is appreciated.

Thanks in advance.

marcus_sommer‌

stalwar1‌

Re: How To Aggregate the value under one

sunny_talwar — Tue, 17 Jul 2018 18:38:49 GMT

Why is 5645333 not showing up? I got this

Using this

LOAD CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium

FROM [Group_No.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

LOAD Only(If(NEW_STATUS = STATUS, CHDRNNUM)) as CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Group By GROUP_NO, Product, NEW_STATUS;

LOAD CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium,

If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

Re: How To Aggregate the value under one

nsetty — Tue, 17 Jul 2018 21:46:08 GMT

GROUP_NO - 8999912 is not repeated, it should not be in the output right?

Re: How To Aggregate the value under one

pra_kale — Wed, 18 Jul 2018 02:19:53 GMT

Hi Sunny,

Thanks for your Help..

The 5645333 won't come only because Product code for this CHDRNUM is CNT and other than Product PPT and GST only policy status IF and PU should be get considered with no Aggregation. This CHDRNUM has status SU so wont't come.

Rest out-put is perfectly OK.

Thanks once again.

Re: How To Aggregate the value under one

pra_kale — Wed, 18 Jul 2018 02:22:11 GMT

Hi,

It will come as it has status IF.

Thanks Nagesh for your Help.

Re: How To Aggregate the value under one

sunny_talwar — Wed, 18 Jul 2018 10:07:09 GMT

May be try this

LOAD CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium

FROM [Group_No.xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

LOAD Only(If(NEW_STATUS = STATUS, CHDRNNUM)) as CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Where Match(NEW_STATUS, 'IF', 'PU')

Group By GROUP_NO, Product, NEW_STATUS;

LOAD CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium,

If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

Re: How To Aggregate the value under one

pra_kale — Thu, 19 Jul 2018 16:11:32 GMT

Hi Sunny,

Thanks for your Help...

Now the thing happened is Grouping is also get's done for all products.

Only if Group_No is repeated and any one policy is having IF status and only for product code PPT and GST then only aggregation is allowed. Though Group_No is repeated but non of the policy is having status IF then aggregation is not allowed.

For rest product codes only policy status IF and PU Should be get considered with No Aggregation.

CHDRNNUM	GROUP_NO	Premium	Product	STATUS
987456	8456123	5500	GST	IF
2345678	803402	3500	PPT	IF
32456789	8999912	56000	PST	IF
	8912346	1065	CNT	IF
	8912346	4927	CNT	PU

Final Out-put should be like this

CHDRNNUM	GROUP_NO	Premium	Product	STATUS
987456	8456123	5500	GST	IF
2345678	803402	3500	PPT	IF
32456789	8999912	56000	PST	IF
98723	8912346	300	CNT	IF
6978	8912346	4500	CNT	PU
56789	8912346	100	CNT	IF
4789	8912346	121	CNT	IF
65478	8912346	223	CNT	IF
78956	8912346	321	CNT	IF
321	8912346	251	CNT	PU
2221	8912346	97	CNT	PU
4555	8912346	79	CNT	PU

Re: How To Aggregate the value under one

sunny_talwar — Thu, 19 Jul 2018 17:47:03 GMT

Try this

LOAD CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium

FROM [Group_No (1).xlsx]

(ooxml, embedded labels, table is Sheet1);

Right Join (A)

LOAD GROUP_NO

Where Flag = 1;

LOAD GROUP_NO,

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

Resident A

Group By GROUP_NO;

Left Join (A)

LOAD GROUP_NO,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, Only(If(STATUS = 'IF', CHDRNNUM))) as NEW_CHDRNNUM,

If(Match(Product, 'PPT', 'GST') and SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 'IF') as NEW_STATUS

Resident A

Group By GROUP_NO, Product;

Final_A:

NoConcatenate

LOAD NEW_CHDRNNUM AS CHDRNNUM,

GROUP_NO,

Sum(Premium) as Premium,

Product,

NEW_STATUS as STATUS

Where Match(NEW_STATUS, 'IF', 'PU')

Group By NEW_CHDRNNUM, GROUP_NO, Product, NEW_STATUS;

LOAD If(Len(Trim(NEW_CHDRNNUM)) > 0, NEW_CHDRNNUM, CHDRNNUM) as NEW_CHDRNNUM,

CHDRNNUM,

GROUP_NO,

Product,

STATUS,

Premium,

If(Len(Trim(NEW_STATUS)) > 0, NEW_STATUS, STATUS) as NEW_STATUS

Resident A;

DROP Table A;

Re: How To Aggregate the value under one

pra_kale — Sat, 21 Jul 2018 07:17:55 GMT

Hi Sunny,

As always you have solution for All...

Thanks for your Help....

Just from understanding point of view of code you have written...pl correct me if i am wrong..

1) In first step by Right joining..you have loaded only Group number which have repeated and at least 1 have status IF that you have loaded..but when i put exist statement after this still I got all the policies except only CHDRNUM having status SU get's excluded..

If(Count(GROUP_NO) > 1 or SubStringCount(Concat(DISTINCT STATUS, '|'), 'IF') = 1, 1, 0) as Flag

2) In Final A Table after Group By you have not put any join then how my condition is get's satisfy "For rest product codes only policy status IF and PU Should be get considered with No Aggregation" . In final table I am not seeing any new columns which you have created NEW_CHDRNUM and NEW_STATUS, how it happened.

Can you pl help me to understand the code...

Thanks Again for Help...

Re: How To Aggregate the value under one

sunny_talwar — Sat, 21 Jul 2018 14:37:36 GMT

1) I will have to take a look at this... may be will check during weekday if you remind me again

2) Creating a new table instead of joining back and renaming NEW_s to the old names...

Also, please mark the appropriate response as the correct response instead of marking your response as correct response. This is not going to be helpful for future visitors

Best,

Sunny

Re: How To Aggregate the value under one

pra_kale — Sat, 21 Jul 2018 15:23:50 GMT

I have wrongly marked my answer as correct answer..Can I make the change now..

And sure I will remind you

Thanks again Sunny.

Re: How To Aggregate the value under one

sunny_talwar — Sat, 21 Jul 2018 16:54:12 GMT

Look here on how to uncorrect an answer

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: How To Aggregate the value under one

pra_kale — Sat, 21 Jul 2018 17:38:05 GMT

Done..Thanks..