Translating into QlikView Script

[karthikoffi27se]

Hi

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

[Anil_Babu_Samineni]

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

[karthikoffi27se]

Hi Anil,

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

Many Thanks

[Anil_Babu_Samineni]

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;

[karthikoffi27se]

Hi Anil,

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

Many Thanks

[karthikoffi27se]

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

[Anil_Babu_Samineni · Accepted solution]

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;