Fletching columns from 2 tables?

[priyarane]

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

[jonathandienst]

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

[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

[#4]

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

[rajeshvaswani77]

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

[priyarane]

Thanks Jonathan,

My scenario is

-

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

[priyarane]

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;

[priyarane]

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;

[priyarane]

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;

[priyarane]

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;