topic Re: Translating into QlikView Script in QlikView

Translating into QlikView Script

karthikoffi27se — Wed, 25 Nov 2020 16:16:04 GMT

I have a requirement to calculate Earned Premium and Unearned Premium, Can you help me to translate the SQL script to QlikView Script.

nEprFactor:=GREATEST(LEAST(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/GREATEST(AddRec.term_dt-AddRec.eff_dt+1,1);

EPR := ROUND(NVL(nEprFactor*nInvPremium,0));

UPR :=nInvPremium-ROUND(NVL(nEprFactor*nInvPremium,0))

Can you Please help me to translate this three script into QlikView script.

Many Thanks

Karthik

Re: Translating into QlikView Script

Anil_Babu_Samineni — Tue, 23 Oct 2018 08:53:15 GMT

May be this? Make sure, Qlik Variables won't support SQL like vice versa

1) LET nEprFactor = MaxString(MinString(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/MaxString(AddRec.term_dt-AddRec.eff_dt+1,1);

2) LET EPR = Round(Alt($(nEprFactor)*$(nInvPremium),0));

3) LET UPR = $(nInvPremium)-ROUND(Alt($(nEprFactor)*$(nInvPremium),0));

Re: Translating into QlikView Script

karthikoffi27se — Tue, 23 Oct 2018 08:57:03 GMT

Hi Anil,

I want this to be a field calculation in the script rather then variable. Can you help me please.

Many Thanks

Re: Translating into QlikView Script

Anil_Babu_Samineni — Tue, 23 Oct 2018 09:31:21 GMT

What does it holds? nInvPremium

Fact:

Load AddRec.term_dt,

dEPRUptoDt,

AddRec.eff_dt,

RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1) as nEprFactor,

Round(Alt(RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1),0)) as EPR,

Your nInvPremium Formula - Round(Alt(RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1),0)) as UPR

From Table_Source;

Re: Translating into QlikView Script

karthikoffi27se — Tue, 23 Oct 2018 09:31:52 GMT

Hi Anil,

It's little urgent request, Can you help me to fetch this as a field in the script.

Many Thanks

Re: Translating into QlikView Script

karthikoffi27se — Tue, 23 Oct 2018 09:33:07 GMT

It's a premium field name, nothing to do with variable.

Re: Translating into QlikView Script

Anil_Babu_Samineni — Tue, 23 Oct 2018 09:37:16 GMT

I've updated above, You can use Field there directly like

Fact:

Load AddRec.term_dt,

dEPRUptoDt,

AddRec.eff_dt,

nInvPremium,

RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1) as nEprFactor,

Round(Alt(RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1),0)) as EPR,

nInvPremium - Round(Alt(RangeMax(RangeMin(AddRec.term_dt,dEPRUptoDt)-AddRec.eff_dt+1,0)/RangeMax(AddRec.term_dt-AddRec.eff_dt+1,1),0)) as UPR

From Table_Source;