Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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));
Hi Anil,
I want this to be a field calculation in the script rather then variable. Can you help me please.
Many Thanks
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;
Hi Anil,
It's little urgent request, Can you help me to fetch this as a field in the script.
Many Thanks
It's a premium field name, nothing to do with variable.
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;