10 Replies Latest reply: Feb 24, 2015 7:48 AM by Priya R RSS

    QVD Updation

    Priya R

      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
          ioannis giakoumakis

          you could use the exists function (and not exists)

          • Re: QVD Updation
            Friedrich Hofmann

            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
                Priya R

                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
                Priya R


                My Script for two qvds are

                 

                 

                 

                FTP:

                LOAD Trad_LEGAL_ENTITY,
                Trad_FTP_ENTITY_CODE
                FROM
                [C:\CCAR DEV\QVDs\TradeDetails\FTPLEMapQVD.qvd]
                (
                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
                [C:\CCAR DEV\QVDs\TradeDetails\TradeDetailForecastQVD.qvd]
                (
                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

                • Re: QVD Updation
                  hariprasad avula

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

                    • Re: QVD Updation
                      Priya R

                      Hi Hari,

                       

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

                        • Re: QVD Updation

                          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

                          [C:\CCAR DEV\QVDs\TradeDetails\FTPLEMapQVD.qvd]

                          (qvd);

                          Concatenate

                          //Trad:

                          LOAD *,

                          2 as Flag

                          FROM

                          [C:\CCAR DEV\QVDs\TradeDetails\TradeDetailForecastQVD.qvd]

                          (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
                              Priya R


                              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,