Data join

[danielnevitt]

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]);

[BrunPierre · Accepted solution]

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;