QVD Updation

[priyarane]

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.

[giakoum]

you could use the exists function (and not exists)

[datanibbler]

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

[priyarane]

Hi Ioannis,

Could you please elobarate if you don't mind

[giakoum]

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

[priyarane]

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.

[priyarane]

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

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,
  

my question is where and how to create my flags

[hariprasadqv]

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

[priyarane]

Hi Hari,

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

[#10]

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)