Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 II
Partner - Master II

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 II
Partner - Master II

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;