Cross table query

Mahamed_Qlik — Mon, 22 Apr 2019 11:12:39 GMT

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;

Re: Cross table query

jyothish8807 — Tue, 23 Apr 2019 07:30:10 GMT

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]

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.

topic Re: Cross table query in QlikView

Cross table query

Re: Cross table query