Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
priyarane
Contributor III

Fletching columns from 2 tables?


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

12 Replies
MVP
MVP

Re: Fletching columns from 2 tables?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Fletching columns from 2 tables?

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

Not applicable

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

rajeshvaswani77
Valued Contributor III

Re: Fletching columns from 2 tables?

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

Re: Fletching columns from 2 tables?

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

Re: Fletching columns from 2 tables?

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

Re: Fletching columns from 2 tables?

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

Re: Fletching columns from 2 tables?

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

Re: Fletching columns from 2 tables?

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;

Community Browser