Data load (SQL and xlxs)

danielnevitt — Wed, 25 Oct 2023 09:43:20 GMT

Hi all,

I would appreciate some help with the below data load.

I' struggling to create a field joining so.operating_desk_cd (from the sql) and CommodityGroup (from the first xlxs table). I would like to name this Join2 and then join with the second xlxs table.

Regards,
Daniel

LOAD *,
first_name & ' ' & last_name as trader_name,
office_cd & ' ' & desk_cd as office_desk_cd,
trade_type_group_cd&'-'&cmdty_cd as Join;

select distinct atr.hdr_num, atr.pcr_num, atr.bfc_cd, atr.co_cd,atr.exchange_cd, atr.strategy_num,atr.trade_dt, atr.trade_num, atr.trade_status_ind, atr.trade_type_group_cd, atr.trader_initials, bs_ind, atr.cmdty_cd,
so.desk_cd, so.office_cd, so.operating_desk_cd

from tempest_tier1.p_edw_trade_all('Non-US','Crude','%','%','%','%','Trade Dt','2023-09-01','2050-12-31','%','Today','Trade',0) atr
left join tempest_tier1.v_STRATEGY_OWNER so on (so.strategy_num = atr.strategy_num)
Where atr.trade_status_ind not in ('C');

LOAD [Trade Type],
Commodity,
CommodityGroup,
Join
FROM
[K:\KPI Dashboards\Trader Mandates\Product Mapping.xlsx]
(ooxml, embedded labels, table is Products);

LOAD operating_desk,
[TradeType],
Mandate,
Join2
FROM
[K:\KPI Dashboards\Trader Mandates\Product Mapping.xlsx]
(ooxml, embedded labels, table is Mandates);

Re: Data load (SQL and xlxs)

WaltShpuntoff — Tue, 31 Oct 2023 11:43:25 GMT

For tables to join, they need to have 1 field in common. (Make sure the capitalization of the field name is the same)

A better way of doing that is to use the ApplyMap function, where it creates internal lookup tables and translates the values.

More information is available here: https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctions/ApplyMap.htm

-ws

Re: Data load (SQL and xlxs)

WaltShpuntoff — Tue, 31 Oct 2023 11:44:20 GMT

One more thing you can try is to use the Data Manager and then review the resulting code.

topic Re: Data load (SQL and xlxs) in App Development

Data load (SQL and xlxs)

Re: Data load (SQL and xlxs)

Re: Data load (SQL and xlxs)