Qlik Community

Qlik Sense App Development

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

Highlighted
carlcimino
Contributor

Pivot multiple columns to rows in load script

Hello I am working on bringing data into QlikSense but need to pivot multiple columns to row values.  I can do so using CrossTable for one field but it doesn't work when I add more fields.  My script below works and pivots SER_NUM_1 - SER_NUM_4 into rows.  However I have fields MODEL_YEAR_1 - MODEL_YEAR_4 (and many other fields) that should pivot as well.  How does one pivot more than one attribute field?  In the screen print you can see that VIN_NUMBER contains stacked VIN and MODEL YEar but I want a column that says Model Year.

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
From stg_prs_qpmis.vw_caasmstr a (nolock)
where ltrim(rtrim(a.POLICY_NO))='15966244'
;
exit script;

 

 

Labels (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Pivot multiple columns to rows in load script

You can first create new fields that are combinations of the model year and serial: MODEL_YR1 & '|' & SER_NUM_1 as FIELD_1 ...etc. These new fields you can pivot using the crosstable function. Then from the table with the pivotted values you need to create a new table where you split the combined values again into separate fields using the subfield function: SubField(VIN_NUMBER, '|' , 1 ) as MODEL_YEAR, SubField(VIN_NUMBER, '|' , 2) as SER_NUM 


talk is cheap, supply exceeds demand
2 Replies
MVP & Luminary
MVP & Luminary

Re: Pivot multiple columns to rows in load script

You can first create new fields that are combinations of the model year and serial: MODEL_YR1 & '|' & SER_NUM_1 as FIELD_1 ...etc. These new fields you can pivot using the crosstable function. Then from the table with the pivotted values you need to create a new table where you split the combined values again into separate fields using the subfield function: SubField(VIN_NUMBER, '|' , 1 ) as MODEL_YEAR, SubField(VIN_NUMBER, '|' , 2) as SER_NUM 


talk is cheap, supply exceeds demand
carlcimino
Contributor

Re: Pivot multiple columns to rows in load script

That is fantastic.  Thank you!