12 Replies Latest reply: Feb 27, 2015 12:43 AM by anbu cheliyan RSS

    Fletching columns from 2 tables?

    Priya R


      Hi Experts,

       

      Is it possible to fletch data from two tables/qvds?

       

      Like:

       

      Qvd1:

      Column1,Column2

      From Qvd1;

       

      Qvd2:

      Column3,Column4

      From Qvd2;

       

      Now I want to create one more qvd/table, its combination of 2 qvds/2tables.

      Like

      Qvd3:

      Qvd1.Column1,Qvd2.Column4

      From Qvd1,Qvd2;

       

      How to make it possible?

       

       

      Please help me

        • Re: Fletching columns from 2 tables?
          Jonathan Dienst

          Hi

           

          You would need to join the tables and to join them, you would need a key field (or fields) that associate the rows in Qvd1 with the correct rows in Qvd2. Unless you actually want a cross-join for some reason, of course.

           

          HTH

          Jonathan

            • Re: Fletching columns from 2 tables?

              QVD3:

              LOAD
              Column1,Column2

              From Qvd1;

               

              OUTER JOIN (QVD3)

              LOAD

              Column3 as column 2 , Column4

              From Qvd2;

               

               

              Note: To join common key field should be there,so renamed column 3 as column2

                • Re: Fletching columns from 2 tables?
                  Priya R

                  Hi,

                   

                  I have two tables and comparing both I need to create flags, Like

                   

                  Qvd1:

                  Column1,Column2;

                  Qvd2:

                  Column3,Column4;

                   

                  Qvd3:

                  if(Qvd1.Column1=Qvd2.Column2,'n','y') as Test_Flag;

                    • Re: Fletching columns from 2 tables?

                      we can't compare two fields in RDBMS way in Qlik as it works on AQL. Personally i solved situations like this by bringing the both fields in one table (through join) then did comparison.

                       

                      Please educate me if any better thoughts

                        • Re: Fletching columns from 2 tables?
                          Priya R

                          Hi Guruprem,

                           

                          I could't solve it in QV and so I took from DB developer's help. He gave CASE statements like below;

                           

                          CASE WHEN (j.FTP_ENTITY_CODE IN (select FTP_ENTITY_CODE_PARENT from RWA..FTP_LE_MAPPING where LEGAL_ENTITY = 'HBUS' ) OR j.FTP_ENTITY_CODE IN (select FTP_ENTITY_CODE_CHILD from RWA..FTP_LE_MAPPING

                          where LEGAL_ENTITY = 'HBUS')) and  (isnull(j.FTP_AFFILIATE,'') NOT IN  (select FTP_ENTITY_CODE_PARENT from RWA..FTP_LE_MAPPING where LEGAL_ENTITY = 'HBUS' ) OR isnull(j.FTP_AFFILIATE,'') NOT IN (select

                          FTP_ENTITY_CODE_CHILD from RWA..FTP_LE_MAPPING where LEGAL_ENTITY = 'HBUS'))
                             then 'Y'
                            else 'N'
                            end   as Trad_IS_HBUS_FLAG

                    • Re: Fletching columns from 2 tables?
                      Priya R

                      Thanks Jonathan,

                       

                      My scenario is

                      -

                      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: Fletching columns from 2 tables?
                        Priya R

                        Hi,

                         

                        I have two tables and comparing both I need to create flags, Like

                         

                        Qvd1:

                        Column1,Column2;

                        Qvd2:

                        Column3,Column4;

                         

                        Qvd3:

                        if(Qvd1.Column1=Qvd2.Column2,'n','y') as Test_Flag;

                      • Re: Fletching columns from 2 tables?
                        Robert Mika

                        qvd1:

                        LOAD Column1,

                             Column2

                        FROM

                        qvd1.xlsx

                        (ooxml, embedded labels, table is Sheet1);

                         

                         

                        qvd2:

                        LOAD Column3,

                             Column4

                        FROM

                        qvd2.xlsx

                        (ooxml, embedded labels, table is Sheet1);

                         


                        qvd3:

                        load

                        Column1

                        Resident qvd1;

                        load Column4

                        Resident qvd2;

                        drop table qvd1, qvd2

                        • Re: Fletching columns from 2 tables?
                          Rajesh Vaswani

                          Hi Priya,

                           

                          You can use the Concatenate keyword to concatenate two tables.

                           

                          Since all the fields are different, the rows would stay not linked but still in the concatenated table.

                           

                          thanks,

                          Rajesh Vaswani