topic Re: QVD Updation in QlikView

QVD Updation

priyarane — Tue, 24 Feb 2015 11:04:02 GMT

Hi Guys,

I have already created one qvd - TRADE. Now I have to create new qvd from other table and Qvd name - FTP

Now I have to update TRAD qvd on the basis of FTP qvd.

Logic:

CASE when(Trad.FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH') and (trade.FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HNAH_FLAG.

how to use this logic by using two qvds. Please help me.

Re: QVD Updation

giakoum — Tue, 24 Feb 2015 11:07:58 GMT

you could use the exists function (and not exists)

Re: QVD Updation

datanibbler — Tue, 24 Feb 2015 11:09:33 GMT

Hi Priya,

I guess I know m.o.l. how to do this and I will tell you.

Independently of that, maybe it would be easier for the users here to help you if you could state your logic in words rather than in code. That will make it easier to reconstruct what you want to do.

Basically, the way to go is the following:

- 1) LOAD TRADE.qvd

- 2) LOAD the contents of FTP.qvd with a WHERE NOT EXISTS clause (to load only those records where the key
does not exist in TRADE.qvd

- 3) Concatenate that (this is actually just a prefix to the second LOAD)

- 4) STORE the resulting table to a new qvd.

HTH

Best regards,

DataNibbler

Re: QVD Updation

priyarane — Tue, 24 Feb 2015 11:10:07 GMT

Hi Ioannis,

Could you please elobarate if you don't mind

Re: QVD Updation

giakoum — Tue, 24 Feb 2015 11:13:35 GMT

See DataNibbler's post :

the second load should have a where statement like that :

where

exists (Trad_LEGAL_ENTITY,'HNAH')

and not exists(FTP_AFFILIATE,'HNAH')

Just an example, test the syntax

Re: QVD Updation

priyarane — Tue, 24 Feb 2015 11:15:22 GMT

Hi Nibbler,

Code is

CASE when(Trad.Trad_FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH') and (trade.Trad_FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HNAH_FLAG,

Note : Trad. is from the Trade Qvd.

Re: QVD Updation

priyarane — Tue, 24 Feb 2015 11:20:47 GMT

My Script for two qvds are

FTP:

LOAD Trad_LEGAL_ENTITY,
Trad_FTP_ENTITY_CODE
FROM

(qvd);

Trad:
LOAD IDENT,
Trad_AS_OF_TMS,
Trad_SCENARIO,
Trad_PERIOD,
Trad_BK_ID,
Trad_PRODUCT_TYPE,
Trad_TRANS_NUMBER,
Trad_ACCT_ID,
Trad_SECURITY_ID,
Trad_ORG_ID,
Trad_CPARTY_ID,
Trad_EXTR_OID,
Trad_GLS_PRODUCT_CODE,
Trad_SOURCE_PRODUCT_CODE,
Trad_PRODUCT_DESC,
Trad_CUST_OID,
Trad_INSTR_ID,
Trad_ACTG_TRN_ID,
Trad_FUTURE_CONTRACT_ID,
Trad_COST_CENTER,
Trad_COMPANY_CODE,
Trad_LEGAL_ENTITY,
Trad_ORG_CODE,
Trad_GHO_CODE,
Trad_N_GHO_CODE,
Trad_N_PRODUCT_GROUP,
Trad_FIRE_PRODUCT_CLASS,
Trad_GRCA_CNTPRTY_CLASS,
Trad_HUSA_Y9_CNTPRTY_CLASS,
Trad_CALL_CNTPRTY_CLASS_SCH_L,
Trad_PFE_PRODUCT,
Trad_MATURITY_BAND,
Trad_CLOSEOUT_AGREEMENT_NUMBER,
Trad_ENTRY_CODE,
Trad_RECORD_SOURCE,
Trad_ORIGINAL_MATURITY,
Trad_REMAINING_MATURITY,
Trad_NORMALIZED_MATURITY,
Trad_EFFECTIVE_MAT_DATE,
Trad_EXCLUSION_RULE_CDE,
Trad_BUY_SELL_IND,
Trad_ACCT_METH_TYPE,
Trad_TRADE_DATE,
Trad_ENTERED_DATE,
Trad_VALUE_DATE,
Trad_MAT_DATE,
Trad_END_DATE,
Trad_REPO_START_DATE,
Trad_REPO_END_DATE,
Trad_CURR_CODE_LONG,
Trad_CURR_CODE_SHORT,
Trad_MATURITY_TYPE,
Trad_ORIGINAL_MATURITY_FLOOR,
Trad_MATURITY_TYPE_FLOOR,
Trad_FX_GOLD_INDICATOR,
Trad_UNDERLYING_ASSET,
Trad_CURR_NOTIONAL,
Trad_FTP_COA_CURR_NTL,
Trad_CURR_BOOK_VALUE,
Trad_FTP_COA_BV,
Trad_ACTL_BOOK_VALUE,
Trad_FTP_COA_ACTL_BV,
Trad_UNREALIZED_PL,
Trad_FTP_COA_UPL,
Trad_MARKET_VALUE_AMT,
Trad_FTP_COA_MV,
Trad_REALIZED_PL,
Trad_FTP_COA_RPL,
Trad_ACCRUED_INTEREST_REC,
Trad_FTP_COA_IR,
Trad_ACCRUED_INTEREST_PAY,
Trad_FTP_COA_IP,
Trad_UNAMORT_PREM_DISC,
Trad_FTP_COA_UAM,
Trad_NOTIONAL_AMT_USD_TRADE_DT,
Trad_UNDERLYING_NOTIONAL_AMT_LONG,
Trad_FTP_COA_LNG_NO,
Trad_UNDERLYING_NOTIONAL_AMT_SHORT,
Trad_FTP_COA_SRT_NO,
Trad_FTP_PRODUCT_CODE,
Trad_FTP_CUSTOMER_CODE,
Trad_FTP_AFFILIATE,
Trad_FTP_SOURCE_SYSTEM,
Trad_ARR_ID,
Trad_N_ACRONYM,
Trad_CCF,
Trad_PREMIUM_PAID_REC_NAV,
Trad_ACTION_ID,
Trad_RULE_PRIORITY,
Trad_CRISP_PARENT_FULLNAME,
Trad_CPARTY_LONG_NAME,
Trad_REC_PAY_FIXED,
Trad_FTP_ENTITY_CODE,
Trad_PARENT,
Trad_RETURN_PAIROFF_NUMBER,
Trad_RETURN_PAIROFF_CUST_GRP_IND,
Trad_COLL_VALUE_WITH_HAIRCUT,
Trad_CONTRACT_VALUE,
Trad_COLLATERAL_VALUE,
Trad_FINANCE_TYPE,
Trad_BLOOMBERG_LOOKUP,
Trad_ISSUE_CURRENCY,
Trad_GFDM_CURRENCY,
Trad_NETTING_FLAG,
Trad_GSCC_ELIGIBLE,
Trad_GSCC_ACCEPTED_FLAG,
Trad_CUSTOMER_NAME,
Trad_CURR_MISMATCH_FLAG,
Trad_GSCC_NETTING_FLAG,
Trad_BOOK_VALUE_SHORT,
Trad_BOOK_VALUE_TRADE,
Trad_BOOK_VALUE_SHORT_TRADE,
Trad_ENT_SHORT_NME,
Trad_CARRYING_VALUE,
Trad_PFE_PRODUCT_UK,
Trad_CCF_UK,
Trad_REMAINING_MATURITY_GFDM,
Trad_CST_NME,
Trad_SUN_RCL_COA_BV,
Trad_SUN_RCL_COA_IR,
Trad_SUN_COA_BV,
Trad_SUN_COA_IR,
Trad_UNNORMALIZED_COLLATERAL,
Trad_TYPE_INSTRUMENT,
Trad_COUNTERPARTY_TYPE,
Trad_COUNTERPARTY_TYPE_OLD,
Trad_CDS_SOLD_IND,
Trad_SUN_COA_IP,
Trad_FITCH_AGENCY_RATING,
Trad_MOODY_AGENCY_RATING,
Trad_SNP_AGENCY_RATING,
Trad_FITCH_STD_RATING,
Trad_MOODY_STD_RATING,
Trad_SNP_STD_RATING,
Trad_US_APPL_RATING,
Trad_UK_APPL_RATING,
Trad_MOODY_GRADE,
Trad_SNP_GRADE,
Trad_FITCH_GRADE,
Trad_US_GRADE,
Trad_UK_GRADE,
Trad_UNDERLYING_ISSUER_TYPE,
Trad_LONG_COMP_NAME,
Trad_BII_CLASSIFICATION,
Trad_BII_EXPOSURE,
Trad_BII_COLLATERAL,
Trad_HAIRCUT_PRCNT,
Trad_HAIRCUT_PRCNT_UK,
Trad_BATCH_ID,
Trad_BATCH_NAME,
Trad_BATCH_DESC,
Trad_EXCLUSION_DESCRIPTION,
Trad_EQY_SIC_CODE,
Trad_BV_GRCA_KEY,
Trad_IR_GRCA_KEY,
Trad_BVS_GRCA_KEY,
Trad_BVT_GRCA_KEY,
Trad_BVST_GRCA_KEY,
Trad_POTENTIAL_FUTURE_EXPOSURE,
Trad_POTENTIAL_FUTURE_EXPOSURE_UK,
Trad_US_APPL_RTG_AGENCY,
Trad_UK_APPL_RTG_AGENCY,
Trad_CURR_FACE_USD,
Trad_LNG_NO_GRCA_KEY,
Trad_SHT_NO_GRCA_KEY,
Trad_UPL_GRCA_KEY,
Trad_ML_RESERVE,
Trad_B_T_IND,
Trad_ALLOCATION_RATIO,
Trad_ISSUER_TYPE_B3,
Trad_HAIRCUT_PRCNT_B3,
Trad_RISK_WEIGHT_B3,
Trad_CCP_FLAG
FROM

(qvd);

Now I have to create flags on basis on two QVDs

Flags are

my question is where and how to create my flags

Re: QVD Updation

hariprasadqv — Tue, 24 Feb 2015 11:50:36 GMT

Use exists or not exists with Id or with time stamp that is fine.

Re: QVD Updation

priyarane — Tue, 24 Feb 2015 11:52:20 GMT

Hi Hari,

Could you please elaborate in my case if you don't mind.

Re: QVD Updation

Tue, 24 Feb 2015 12:20:33 GMT

Hi,

Can you concatenate both table with flag and create your required flag( like below).

FTP:

LOAD Trad_LEGAL_ENTITY,

Trad_FTP_ENTITY_CODE,

1 as Flag

FROM

(qvd);

Concatenate

//Trad:

LOAD *,

2 as Flag

FROM

(qvd);

FTP1:

LOAD *,

case1 as Trad_IS_HNAH_FLAG,

case2 as Trad_IS_HUSI_FLAG

Resident FTP;

DROP Table FTP;

Points--

case1-put your condtion by using (if,match)

case2-put your condtion by using (if,match)

Re: QVD Updation

priyarane — Tue, 24 Feb 2015 12:48:11 GMT

Thanks Vimlesh,

In my case I have 8 Cases so, I have to devide 4(FTP) and 4(Trade). Please suggest me

CASE when(trade.Trad_FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH') and (trade.Trad_FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HNAH_FLAG,

CASE when(trade.Trad_FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HUSI') and (trade.Trad_FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HUSI'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HUSI_FLAG,

CASE when(trade.Trad_FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBUS') and (trade.Trad_FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBUS'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HBUS_FLAG,

CASE when(trade.FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBIO') and (trade.FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBIO'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HBIO_FLAG,

CASE when(trade.FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HMUS') and (trade.FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HMUS'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HMUS_FLAG,

CASE when(trade.FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBON') and (trade.FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HBON'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HBON_FLAG,

CASE when(trade.FTP_ENTITY_CODE IN ( select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH') and (trade.FTP_AFFILIATE,'') NOT IN (select Trad_FTP_ENTITY_CODE from FTPLEMapQVD where Trad_LEGAL_ENTITY = 'HNAH'))
   then 'Y'
   else 'N'
   end   as Trad_IS_HNAH_FLAG,

'Y' as Trad_IS_CONS_FLAG,