Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I’ve this situation where policy can have child policy and found as below:
policy | premium |
EANY33262 | 818.88 |
XVPA1421 | 10717.3 |
XVPA1421A | 791.16 |
XVPA1421B | 1168.08 |
XVPA1421C | 6361.16 |
XVPA1421D | 1017.3 |
XVPA1421E | |
XVPA1421F | 19.68 |
XVPA1421X | 0 |
XVPA1421H | 7111.88 |
XVPA33707 | 310.08 |
XVPA33749 | 1110.88 |
D43026 | 0 |
B278642 | -928.8 |
Also can be one master policy without child, i.e.; XVSC35903, B278642. I’m planning to create new fields (2) for policy and premium where it will populate the master policy numbers only however will sum the premium for all the sub policies/ child they may have. Premium associated with them may or may not have numbers and I need to sum for all the child/ sub-policies into the primary one, e.g.; XVPA1421 and should show premium as 27186.56 and while count should XVPA1421 once than 9.
I’m with very minimal knowledge with Qlik and simply hoping some could help with the script to load the new fields. I sincerely appreciate your effort and assistance.
Best regards,
Jami
I'll assume that what makes a Sub-Policy is that the rightmost character is not numeric. If so you could derive a new MasterPolicy field like this:
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy
A complete script to sum premiums by MasterPolicy could be this below . Note that it uses a preceding load. The bottom LOAD is executed first and the output is passed up to the top LOAD.
Policies:
LOAD
MasterPolicy,
sum(premium) as premium,
sum(1) as PolicyCount
Group by MasterPolicy
;
LOAD
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy,
premium
FROM [lib://QCSample]
(html, utf8, embedded labels, table is @1);
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
I'll assume that what makes a Sub-Policy is that the rightmost character is not numeric. If so you could derive a new MasterPolicy field like this:
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy
A complete script to sum premiums by MasterPolicy could be this below . Note that it uses a preceding load. The bottom LOAD is executed first and the output is passed up to the top LOAD.
Policies:
LOAD
MasterPolicy,
sum(premium) as premium,
sum(1) as PolicyCount
Group by MasterPolicy
;
LOAD
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy,
premium
FROM [lib://QCSample]
(html, utf8, embedded labels, table is @1);
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Thank you very much Rob, it's making sense when i ran.
Additionally now that I want to load the other fields from QVD to my table such as below, I receive error msg: 'Invalid expression: LOAD if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy, policy, premium, carrier FROM [lib://Qlik Data/Production4.qvd](qvd)'
Policies:
LOAD
field1,
field2,
fieldn,
MasterPolicy,
sum(premium) as premium,
sum(1) as PolicyCount
Group by MasterPolicy
;
LOAD
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy,
policy,
premium,
field1,
field2,
fieldn
FROM [lib://Qlik Data/ProductionX.qvd](qvd);
Where should I list those fields? Thank you again for your help.
If you want one row per MasterPolicy, those additional fields will either have to use an aggregation function or be included in the Group By list. Like:
Group By MasterPolicy, field1, field2, fieldn.
An alternative to is to maintain two tables, one for Policies and one for Master Policy totals. This is a bit easier if it gives you what you want.
SubPolicies:
LOAD
if(IsNum(right(policy, 1)), policy, left(policy, len(policy)-1)) as MasterPolicy,
policy,
premium
FROM [lib://QCSample]
(html, utf8, embedded labels, table is @1);
MasterPolicies:
LOAD
MasterPolicy,
sum(premium) as TotalPremium,
sum(1) as PolicyCount
Resident SubPolicies
Group by MasterPolicy
;
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com