Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mahamedfaijan
Creator II
Creator II

Cross table query

Hi All,

 

Please help me to understand below crosstable script :

 

BARTemp:
NoConcatenate Load
autonumber(text(CONTR_ID) & '-' & text(CLM_FILE_ID)) as QVClaimID
,RowNo() as LossID
,text(CONTR_ID) as [Policy Number]
,text(CLM_FILE_ID) as [Claim Number]
,num(ACTG_MO) as [Account Month]
,num(ACTG_YR) as [Account Year]
,num(INCU_ALAE_US_CURY_AMT) as ALAE
,num(PD_INDM_US_CURY_AMT) as [Paid Loss]
,num(OS_INDM_RES_US_CURY_AMT) as OS
,num(COMM_AMT) as Commission
,num(WR_PREM_AMT) as [Written Premium]
,num(INCU_ALAE_US_CURY_AMT) + num(PD_INDM_US_CURY_AMT) + num(OS_INDM_RES_US_CURY_AMT) as [Incurred Loss]
,floor(date(CONTR_EFF_DT, 'MM/DD/YYYY')) as [Contract Effective Date]
,floor(date(CONTR_XPIR_DT, 'MM/DD/YYYY')) as [Contract Expiration Date]
, NEW_RNWL_CD
, CUR_IND
, INS_CD
, INSD_NM
, MCC_CD
, BUS_CLSN_STRC_CD
, CONTR_LN_CD
, PRDT_CD
, num(ACTG_MO) as AccountMonth
, num(ACTG_YR) as AccountYear
Resident BAR_Full;

drop table BAR_Full;

BAR_Fact:
CrossTable ([Loss Attribute], [Loss Attribute Value],6)
Load
*
Resident BARTemp;

drop table BARTemp;

1 Reply
jyothish8807
Master II
Master II

Hi Mahamed,

As per the logic:

Below will be filed in the data model:

1. QVClaimID

2.LossID

3. [Policy Number]

4. [Claim Number]

5. [Account Month]

6.  [Account Year]

A new field  "[Loss Attribute]" is created now which will have below values:

ALAE
[Paid Loss]
OS
Commission
[Written Premium]
[Incurred Loss]
 [Contract Effective Date]
[Contract Expiration Date]
 NEW_RNWL_CD
 CUR_IND
 INS_CD
 INSD_NM
 MCC_CD
 BUS_CLSN_STRC_CD
 CONTR_LN_CD
 PRDT_CD
AccountMonth

 

One more new field "[Loss Attribute Value]" will be created which will contain the values of all the field mentioned above

https://www.youtube.com/watch?v=1xXcoi9buLM

This tutorial will be helpful.

Best Regards,
KC