topic Re: Fletching columns from 2 tables? in QlikView

Fletching columns from 2 tables?

priyarane — Tue, 24 Feb 2015 13:02:59 GMT

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.

Qvd3:

Qvd1.Column1,Qvd2.Column4

From Qvd1,Qvd2;

How to make it possible?

Please help me

Re: Fletching columns from 2 tables?

jonathandienst — Tue, 24 Feb 2015 13:06:24 GMT

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?

robert_mika — Tue, 24 Feb 2015 13:20:10 GMT

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?

Tue, 24 Feb 2015 13:21:59 GMT

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?

rajeshvaswani77 — Tue, 24 Feb 2015 13:23:07 GMT

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

Re: Fletching columns from 2 tables?

priyarane — Tue, 24 Feb 2015 13:26:44 GMT

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

Re: Fletching columns from 2 tables?

priyarane — Tue, 24 Feb 2015 13:35:34 GMT

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?

priyarane — Tue, 24 Feb 2015 13:36:09 GMT

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?

priyarane — Tue, 24 Feb 2015 13:36:39 GMT

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?

priyarane — Tue, 24 Feb 2015 13:37:09 GMT

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?

Tue, 24 Feb 2015 16:23:11 GMT

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?

priyarane — Thu, 26 Feb 2015 10:01:33 GMT

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?

anbu1984 — Fri, 27 Feb 2015 05:43:51 GMT

Qvd1:

Column1,Column2;

Left Join(Qvd1)

Qvd2:

Column3 As Column1,Column4, 1 As Dummy;

Qvd3:

if(IsNull(Dummy),'y','n') as Test_Flag;