Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Script to load the new fields

Hi

I’ve this situation where policy can have child policy and found as below:

policypremium
EANY33262818.88
XVPA1421 10717.3
XVPA1421A791.16
XVPA1421B1168.08
XVPA1421C6361.16
XVPA1421D1017.3
XVPA1421E 
XVPA1421F19.68
XVPA1421X0
XVPA1421H7111.88
XVPA33707310.08
XVPA337491110.88
D430260
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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
Contributor III
Contributor III

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.

Highlighted
MVP & Luminary
MVP & Luminary

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
;

2019-02-01_16-13-05.png

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com