Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
carlcimino
Contributor

Iterative Calculation during Load in Qlik

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;

 

 

Labels (3)
3 Replies
MVP & Luminary
MVP & Luminary

Re: Iterative Calculation during Load in Qlik

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

 

-Rob

carlcimino
Contributor

Re: Iterative Calculation during Load in Qlik

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;

 

 

carlcimino
Contributor

Re: Iterative Calculation during Load in Qlik