Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
priyarane
Specialist
Specialist

QVD Updation

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.

10 Replies
giakoum
Partner - Master II
Partner - Master II

you could use the exists function (and not exists)

datanibbler
Champion
Champion

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
Specialist
Specialist
Author

Hi Ioannis,

Could you please elobarate if you don't mind

giakoum
Partner - Master II
Partner - Master II

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author


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
Creator III
Creator III

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

priyarane
Specialist
Specialist
Author

Hi Hari,

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

Not applicable

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)