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