topic Re: $SYN in QlikView

$SYN

sagaraperera — Wed, 29 Apr 2015 07:09:52 GMT

I want to withdraw $ syn are cause it to below picture. Why are the sketch and description, claims a table number. Method to clarify concerns, and to make the table from $ syn.

SAGARA

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='Rs#,##0.00;(Rs#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Directory;

CrossTable(DESCRIPTION,BUDGET, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LBUDDATA2011_2014.QVD]

(qvd);

CrossTable(DESCRIPTION, BUDGET, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LBUDDATA2015.QVD]

(qvd);

CrossTable(DESCRIPTION, Data, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LDATA2011_2014.QVD]

(qvd);

CrossTable(DESCRIPTION, Data, 6)

LOAD BCOD,

MONTH,

YEAR,

MONTH2,

CLA_CODE,

MONM,

GROSS_PREMIUM,

SRCC_PREMIUM,

REINSURANCE_FUND,

FOREIGN_COMPANIES,

CEEDED_PREMIUM,

NRP,

UPR_CHARGE,

NEP,

SRCC_TC_COMMISSION,

FOREIGN_CO_COMMISSION,

COMMISSION_INCOME,

NET_REVENUE,

GROSS_CLAIMS,

CLAIMS_RES_FUND,

EXGRATIA_CLIMS_RES_FUND,

CLAIMS_PROCESSING_EXPENSES,

LEGAL_FEES,

RI_RECOVERIES_RI_FUND,

RI_RECOVERIES_SRCC_TC,

RI_RECOVERIES_FOREIGN,

SALVAGE_RECOVERIES,

NET_CLAIMS,

COMMISSION_EXPENSES,

PRODUCTION_BONUS,

DAC_CHARGE,

ACQUISITION_COST,

GROSS_PROFIT,

STAFF_EXPENSES,

ADMIN_EXPENSES,

SELLING_EXPENSES,

FINANCIAL_EXPENSES,

DEPRICIATION_EXPENSES,

MANAGEMENT_EXPENSES,

INV_INCOME_EXT,

INV_INCOME_INT,

OTHER_INCOME_EXT,

OTHER_INCOME_INT,

INV_OTHER_INCOME,

NET_PROFIT

FROM

[..\..\qlickviwe\P&L\P&LDATA2015.QVD]

(qvd);

LOAD BCOD,

NO,

ACCOUNTANT,

AC_NO,

REGION,

BRA_CAT,

BRA_DBASE,

BRANCH,

DEPT

FROM

[..\..\qlickviwe\Branch.qvd]

(qvd);

Directory;

LOAD PRD_CODE,

CLA_CODE,

PRODUCT,

CLASS,

MO_NM,

IBNR_CLASS

FROM

[..\..\qlickviwe\CLASS_PRD.qvd]

(qvd);

Directory;

LOAD CLA_CODE,

CLASS

FROM

[..\..\qlickviwe\Class.qvd]

(qvd);

Re: $SYN

petter — Wed, 29 Apr 2015 07:42:19 GMT

You have to get rid of your synthetic keys - hence the $SYN ....

I think I might have a quick fix for you here - my changes are at line 1,8,9, 54, 61,62,107,114,115,160,167,168,

226,228,230 and 240. Don't mind the red ... it some error on the Jive editor for the Qlik Community Site...

CrossTable(DESCRIPTION,Data, 😎
LOAD BCOD,
     MONTH,
     YEAR,
     MONTH2,
     CLA_CODE,
     MONM,
'BUDGET' AS Type,
FileName() AS Source,
     GROSS_PREMIUM,
     SRCC_PREMIUM,
     REINSURANCE_FUND,
     FOREIGN_COMPANIES,
     CEEDED_PREMIUM,
     NRP,
     UPR_CHARGE,
     NEP,
     SRCC_TC_COMMISSION,
     FOREIGN_CO_COMMISSION,
     COMMISSION_INCOME,
     NET_REVENUE,
     GROSS_CLAIMS,
     CLAIMS_RES_FUND,
     EXGRATIA_CLIMS_RES_FUND,
     CLAIMS_PROCESSING_EXPENSES,
     LEGAL_FEES,
     RI_RECOVERIES_RI_FUND,
     RI_RECOVERIES_SRCC_TC,
     RI_RECOVERIES_FOREIGN,
     SALVAGE_RECOVERIES,
     NET_CLAIMS,
     COMMISSION_EXPENSES,
     PRODUCTION_BONUS,
     DAC_CHARGE,
     ACQUISITION_COST,
     GROSS_PROFIT,
     STAFF_EXPENSES,
     ADMIN_EXPENSES,
     SELLING_EXPENSES,
     FINANCIAL_EXPENSES,
     DEPRICIATION_EXPENSES,
     MANAGEMENT_EXPENSES,
     INV_INCOME_EXT,
     INV_INCOME_INT,
     OTHER_INCOME_EXT,
     OTHER_INCOME_INT,
     INV_OTHER_INCOME,
     NET_PROFIT
FROM
[..\..\qlickviwe\P&L\P&LBUDDATA2011_2014.QVD]
(qvd);

CrossTable(DESCRIPTION, Data, 😎
LOAD BCOD,
     MONTH,
     YEAR,
     MONTH2,
     CLA_CODE,
     MONM,
'BUDGET' AS Type,
FileName() AS Source,
     GROSS_PREMIUM,
     SRCC_PREMIUM,
     REINSURANCE_FUND,
     FOREIGN_COMPANIES,
     CEEDED_PREMIUM,
     NRP,
     UPR_CHARGE,
     NEP,
     SRCC_TC_COMMISSION,
     FOREIGN_CO_COMMISSION,
     COMMISSION_INCOME,
     NET_REVENUE,
     GROSS_CLAIMS,
     CLAIMS_RES_FUND,
     EXGRATIA_CLIMS_RES_FUND,
     CLAIMS_PROCESSING_EXPENSES,
     LEGAL_FEES,
     RI_RECOVERIES_RI_FUND,
     RI_RECOVERIES_SRCC_TC,
     RI_RECOVERIES_FOREIGN,
     SALVAGE_RECOVERIES,
     NET_CLAIMS,
     COMMISSION_EXPENSES,
     PRODUCTION_BONUS,
     DAC_CHARGE,
     ACQUISITION_COST,
     GROSS_PROFIT,
     STAFF_EXPENSES,
     ADMIN_EXPENSES,
     SELLING_EXPENSES,
     FINANCIAL_EXPENSES,
     DEPRICIATION_EXPENSES,
     MANAGEMENT_EXPENSES,
     INV_INCOME_EXT,
     INV_INCOME_INT,
     OTHER_INCOME_EXT,
     OTHER_INCOME_INT,
     INV_OTHER_INCOME,
     NET_PROFIT
FROM
[..\..\qlickviwe\P&L\P&LBUDDATA2015.QVD]
(qvd);

CrossTable(DESCRIPTION, Data, 😎
LOAD BCOD,
     MONTH,
     YEAR,
     MONTH2,
     CLA_CODE,
     MONM,
'ACTUAL' AS Type,
FileName() AS Source,
     GROSS_PREMIUM,
     SRCC_PREMIUM,
     REINSURANCE_FUND,
     FOREIGN_COMPANIES,
     CEEDED_PREMIUM,
     NRP,
     UPR_CHARGE,
     NEP,
     SRCC_TC_COMMISSION,
     FOREIGN_CO_COMMISSION,
     COMMISSION_INCOME,
     NET_REVENUE,
     GROSS_CLAIMS,
     CLAIMS_RES_FUND,
     EXGRATIA_CLIMS_RES_FUND,
     CLAIMS_PROCESSING_EXPENSES,
     LEGAL_FEES,
     RI_RECOVERIES_RI_FUND,
     RI_RECOVERIES_SRCC_TC,
     RI_RECOVERIES_FOREIGN,
     SALVAGE_RECOVERIES,
     NET_CLAIMS,
     COMMISSION_EXPENSES,
     PRODUCTION_BONUS,
     DAC_CHARGE,
     ACQUISITION_COST,
     GROSS_PROFIT,
     STAFF_EXPENSES,
     ADMIN_EXPENSES,
     SELLING_EXPENSES,
     FINANCIAL_EXPENSES,
     DEPRICIATION_EXPENSES,
     MANAGEMENT_EXPENSES,
     INV_INCOME_EXT,
     INV_INCOME_INT,
     OTHER_INCOME_EXT,
     OTHER_INCOME_INT,
     INV_OTHER_INCOME,
     NET_PROFIT
FROM
[..\..\qlickviwe\P&L\P&LDATA2011_2014.QVD]
(qvd);

CrossTable(DESCRIPTION, Data, 😎
LOAD BCOD,
     MONTH,
     YEAR,
     MONTH2,
     CLA_CODE,
     MONM,
'ACTUAL' AS Type,
FileName() AS Source,
     GROSS_PREMIUM,
     SRCC_PREMIUM,
     REINSURANCE_FUND,
     FOREIGN_COMPANIES,
     CEEDED_PREMIUM,
     NRP,
     UPR_CHARGE,
     NEP,
     SRCC_TC_COMMISSION,
     FOREIGN_CO_COMMISSION,
     COMMISSION_INCOME,
     NET_REVENUE,
     GROSS_CLAIMS,
     CLAIMS_RES_FUND,
     EXGRATIA_CLIMS_RES_FUND,
     CLAIMS_PROCESSING_EXPENSES,
     LEGAL_FEES,
     RI_RECOVERIES_RI_FUND,
     RI_RECOVERIES_SRCC_TC,
     RI_RECOVERIES_FOREIGN,
     SALVAGE_RECOVERIES,
     NET_CLAIMS,
     COMMISSION_EXPENSES,
     PRODUCTION_BONUS,
     DAC_CHARGE,
     ACQUISITION_COST,
     GROSS_PROFIT,
     STAFF_EXPENSES,
     ADMIN_EXPENSES,
     SELLING_EXPENSES,
     FINANCIAL_EXPENSES,
     DEPRICIATION_EXPENSES,
     MANAGEMENT_EXPENSES,
     INV_INCOME_EXT,
     INV_INCOME_INT,
     OTHER_INCOME_EXT,
     OTHER_INCOME_INT,
     INV_OTHER_INCOME,
     NET_PROFIT
FROM
[..\..\qlickviwe\P&L\P&LDATA2015.QVD]
(qvd);

LOAD BCOD,
     NO,
     ACCOUNTANT,
     AC_NO,
     REGION,
     BRA_CAT,
     BRA_DBASE,
     BRANCH,
     DEPT
FROM
[..\..\qlickviwe\Branch.qvd]
(qvd);
Directory;
LOAD
CLA_CODE & '-' & CLASS AS %ClassKey
     PRD_CODE,
     CLA_CODE AS PRD_CLA_CODE,
     PRODUCT,
     CLASS AS PRD_CLASS,
     MO_NM,
     IBNR_CLASS
FROM
[..\..\qlickviwe\CLASS_PRD.qvd]
(qvd);

Directory;
LOAD
CLA_CODE & '-' & CLASS AS %ClassKey
     CLA_CODE,
     CLASS
FROM
[..\..\qlickviwe\Class.qvd]
(qvd);

Re: $SYN

petter — Wed, 29 Apr 2015 07:52:29 GMT

It also seems that you have been a victim of a QVD-loading bug that materialize when you do a CROSSTABLE LOAD. The fix is to add a seemingly unneccessary WHERE 1=1 at the of each FROM .... qvd (qvd) ....; like this

FROM

[ xxxxxxx.qvd ] (qvd)

WHERE 1=1;

For each of your four CROSSTABLE (....) LOAD's.