Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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