Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
danielnevitt
Creator
Creator

Data join

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

Labels (1)
1 Solution

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

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;

View solution in original post

1 Reply
BrunPierre
Partner - Master
Partner - Master

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;