topic Re: Iterative Calculation during Load in Qlik in App Development

Iterative Calculation during Load in Qlik

carlcimino — Sat, 16 Nov 2024 07:09:19 GMT

Pulling some data that has 4 columns that I want to turn into 4 rows. I started by using an iterative loop where i = 1 to 4. However I want to use the $(i) within a formula to calculate the weighting of each base prem 1-4 to the total. As soon as I insert this into the SQL I get an error - my script log is attached. Any help would be greatly appreciated.

(a.BASE_PREM$(i)/(a.BASE_PREM1+a.BASE_PREM2+a.BASE_PREM3+a.BASE_PREM4))*a.TOT_TRM_PRM as VEH_TRM_PRM

Here's my working script before adding the formula.

LIB CONNECT TO 'PRS_Auto_Inforce_AIP';

for i = 1 to 4

CAAS:
load
*
where len(trim(MODEL_YEAR))>0
;
Select
cast(replicate('0',12-len(ltrim(rtrim(a.POLICY_NO))))+ltrim(rtrim(a.POLICY_NO)) as varchar(99)) as POLICY_NUMBER
, replicate('0',7-len(a.AGENT_NO))+a.AGENT_NO as PROD_N
, a.SUB_AGENT as SUB_PROD_N
, a.COMPANY_CODE
, DATEADD(YEAR, -1,a.EXPRN_DATE) as EFFECTIVE_DATE
, a.EXPRN_DATE as EXPIRATION_DATE
, left(a.AS_OF_YYYYMMDD,6) as AS_OF_YYYYMM
, a.NO_OF_CARS as NUMBER_OF_VEHICLES
, a.NO_OF_DRVRS as NUMBER_OF_DRIVERS
, a.SCR_SCORE as INSURANCE_SCORE
, a.NAME_INSD1 as INSURED_NAME
, a.INSD_STREET as MAILING_ADDRESS
, a.INSD_CITY as MAILING_CITY
, a.INSD_STATE as MAILING_STATE
, a.INSD_ZIP as MAILING_ZIPCODE
, $(i) as UNIT_ID
, a.MODEL_YR$(i) as MODEL_YEAR
, case
when isnull(a.MODEL_YR$(i),0)=0 then 'UNKNOWN'
when a.MODEL_YR$(i)>=2010 then '2010+'
when a.MODEL_YR$(i)>=1990 then '1990-2010'
when a.MODEL_YR$(i)<1990 then '<1990'
end as MODEL_YEAR_GROUP
, a.AUTO_TYPE$(i) as VEHICLE_TYPE
, a.USE_CODE$(i) as VEHICLE_USE
, a.RATE_SYM_COMP_ISO$(i) as COMP_SYMBOL
, a.RATE_SYM_COLL_ISO$(i) as COLLISION_SYMBOL
, case when a.GAR_STATE_$(i) is null then a.INSD_CNTY else a.STATE_CODE end as GARAGE_STATE
, case when a.GAR_ZIP_$(i) is null then a.ZIP_CODE else a.GAR_ZIP_$(i) end as GARAGE_ZIPCODE
, case when a.GAR_COUNTY_$(i) is null then a.INSD_CNTY else a.GAR_COUNTY_$(i) end as GARAGE_COUNTY
, case when a.GAR_CITY_$(i) is null then a.INSD_CITY else a.GAR_CITY_$(i) end as GARAGE_CITY
, a.CAR_TERR$(i) as TERRITORY
, a.COLL_DED$(i) as COLLISION_DEDUCTIBLE
, a.COMP_DED$(i) as COMP_DEDUCTIBLE
, a.PASS_RES_$(i) as PASSIVE_RESTRAINT
, a.SER_NUM_$(i) as VIN_NUMBER
, a.AGREED_VAL$(i) as AGREED_VALUE
, 'Chubb' as LEGACY_COMPANY
, a.TOT_TRM_PRM as AUTO_ANNUAL_PREMIUM
From stg_prs_qpmis.vw_caasmstr a (nolock)
;

Store CAAS into 'lib://PRS_Auto_Inforce_QVD/CAAS_CAR_$(i).qvd' (qvd);

drop table CAAS;
next i;

Re: Iterative Calculation during Load in Qlik

rwunderlich — Fri, 30 Nov 2018 21:47:57 GMT

To turn columns into rows you will want to use the CrossTable load prefix. Read up on CrossTable in the help:

-Rob

Re: Iterative Calculation during Load in Qlik

carlcimino — Mon, 03 Dec 2018 17:40:56 GMT

Thanks Rob! I got the crosstable load to work for transposing 4 columns to 1. When I add another set of 4 columns (highlighted below) the script runs but it returns nothing for those 4 new columns. Is there something else I have to qualify?

crosstable(UNIT_ID, VIN_NUMBER, 16)
load *
;
Select
cast(replicate('0',12-len(ltrim(rtrim(a.POLICY_NO))))+ltrim(rtrim(a.POLICY_NO)) as varchar(99)) as POLICY_NUMBER
, replicate('0',7-len(a.AGENT_NO))+a.AGENT_NO as PROD_N
, a.SUB_AGENT as SUB_PROD_N
, a.COMPANY_CODE
, DATEADD(YEAR, -1,a.EXPRN_DATE) as EFFECTIVE_DATE
, a.EXPRN_DATE as EXPIRATION_DATE
, left(a.AS_OF_YYYYMMDD,6) as AS_OF_YYYYMM
, a.NO_OF_CARS as NUMBER_OF_VEHICLES
, a.NO_OF_DRVRS as NUMBER_OF_DRIVERS
, a.SCR_SCORE as INSURANCE_SCORE
, a.NAME_INSD1 as INSURED_NAME
, a.INSD_STREET as MAILING_ADDRESS
, a.INSD_CITY as MAILING_CITY
, a.INSD_STATE as MAILING_STATE
, a.INSD_ZIP as MAILING_ZIPCODE
, a.TOT_TRM_PRM
, a.SER_NUM_1
, a.SER_NUM_2
, a.SER_NUM_3
, a.SER_NUM_4
, a.MODEL_YR1
, a.MODEL_YR2
, a.MODEL_YR3
, a.MODEL_YR4
From stg_prs_qpmis.vw_caasmstr a (nolock)
where ltrim(rtrim(a.POLICY_NO))='15966244'
;
exit script;

Re: Iterative Calculation during Load in Qlik

carlcimino — Wed, 05 Dec 2018 19:54:50 GMT

this was solved in the following thread by @Gysbert_Wassenaar

https://community.qlik.com/t5/Qlik-Sense-App-Development/Pivot-multiple-columns-to-rows-in-load-script/m-p/1516955#M36821