Data join

danielnevitt — Tue, 25 Apr 2023 18:31:55 GMT

Dear all,

I have the below sql and I would like to create a field taking some fields from the sql load and some from the table.

Is this possible?

Ideally I would like to do: exchange_cd&'-'&[CLEARING ADMIN] as JoinCode

LOAD *,
exchange_cd&'-'&[CLEARING ADMIN] as JoinCode;

select distinct atr.hdr_num,atr.hdr_prcg_end_dt,atr.hdr_prcg_start_dt,atr.pcr_num,atr.period_cd,atr.bfc_cd,atr.broker_cd,atr.clearing_acct_cd,atr.co_cd,atr.co_create_dt,atr.create_initials,atr.efp_ind,atr.exchange_cd,atr.final_pricing_dt,atr.internal_co_cd,atr.internal_name,atr.payment_qty,atr.pos_lots,atr.pos_vol,abs(atr.pos_vol) as abs_pos_vol,atr.strategy_num,atr.trade_dt,atr.trade_num,atr.trade_status_ind,atr.trade_type_group_cd,atr.trade_type_name,atr.trader_initials,atr.our_reference,

from tempest_tier1.p_edw_trade_all('Non-US','%','%','%','%','%','Trade Dt','2023-03-01','2050-12-31','%','Today','Trade',0) atr

LEFT JOIN LOAD [PRODUCT (Click to open in Browser)],
[PRODUCT ID],
PHYSICAL as udf_exchange_cd,
GROUP,
[CLEARING ADMIN],
[CLEARING VENUE],
[MIC CODE],
[MARKET TYPE NAME]
FROM
[Z:\QlikView\Market Conduct 2020\ICE Product Codesv2.xlsx]
(ooxml, embedded labels, table is [ICE Product Codes]);

Re: Data join

BrunPierre — Tue, 25 Apr 2023 20:10:20 GMT

Hi, you would have to join both tables and then you can perform the transformations/calculations from the resultant table. Something like this.

Table1: SELECT DISTINCT exchange_cd, ... FROM ..... Left Join(Table1) Table2: LOAD PHYSICAL as exchange_cd, [CLEARING ADMIN], ... FROM ... NoConcatenate FinalTable: LOAD ..., exchange_cd&'-'&[CLEARING ADMIN] as JoinCode Resident Table1; DROP Table Table1;

topic Data join in App Development

Data join

Re: Data join