Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Simple joins

Hello

Tried to look at help fucntions on this but I am not tthat famiiliar with SQL

I would like to joing to tables in order to get away from some loops , Im confused between Join Select and Join load . Two tables are ( jpisn arte highlighted fields

MasterAuM:

LOAD "aum_id",

"aum_bbtick" as %Key_AuMStatic,

"aum_isin",

"FUND_TOTAL_ASSETS",

"FUND_TOTAL_ASSETS_CRNCY",

"FUND_TOTAL_ASSETS_EUR",

"mastproav_Impid",

"FUND_TOTAL_ASSETS_DT" as %AumDate,

Month("FUND_TOTAL_ASSETS_DT") as Month,

Year("FUND_TOTAL_ASSETS_DT") as Year,

Week("FUND_TOTAL_ASSETS_DT") as Week;

SQL SELECT *

FROM AUM.dbo.masterprodaumvol;

MasterNAV:

Load BBTicker as %Key_NAVStatic,

FUND_NET_ASSET_VAL as NAV,

NAV_CRNCY as NAV_Curr,

FUND_NET_ASSET_VAL_EUR as NAV_EUR,

FUND_NAV_DT as NAV_Date;

SQL SELECT *

FROM AUM.dbo.masterprodaumvol;

Thanks







1 Solution

Accepted Solutions
Not applicable
Author

I would do LEFT JOIN ("Table 1") LOAD

Key, FieldA,FieldB, FieldC; SQL SELECT xxxxxxx FROM tablexxxxx;

Then I would delete fields I don't need in Table 1.

Hope this will help

View solution in original post

4 Replies
Miguel_Angel_Baeyens

Hello,

Do you want to load both tables from your SQL source in one for QlikView? If so, use CONCATENATE, as it seems that both tables MasterAuM and MasterNAV are very similar. Should you want to separate (for searching or filtering purposes) the source of both tables, you can use Autonumber() function. Most simple way to do it is:


MasterAuM:
LOAD
"aum_id",
Autonumber('AUM ' & "aum_bbtick") as KEY,
"aum_isin",
"FUND_TOTAL_ASSETS",
"FUND_TOTAL_ASSETS_CRNCY",
"FUND_TOTAL_ASSETS_EUR",
"mastproav_Impid",
"FUND_TOTAL_ASSETS_DT" as Date,
Month("FUND_TOTAL_ASSETS_DT") as Month,
Year("FUND_TOTAL_ASSETS_DT") as Year,
Week("FUND_TOTAL_ASSETS_DT") as Week;
SQL SELECT *
FROM AUM.dbo.masterprodaumvol;
CONCATENATE
Load
Autonumber('NAV' & BBTicker) as KEY,
FUND_NET_ASSET_VAL as NAV,
NAV_CRNCY as NAV_Curr,
FUND_NET_ASSET_VAL_EUR as NAV_EUR,
FUND_NAV_DT as Date;
SQL SELECT *
FROM AUM.dbo.masterprodaumvol;


Fields that don't exist in both tables will be filled with null unless otherwise is specified. Autonumber would help you to identify the source in your further scripting.

Regards.

Not applicable
Author

Hi Miguel Thanks for the answer , ideally I would like to manage from edit script as suggested but my new table would look like ( with red highlight where data is the same ). Essentiually I want to take 4 fields from table 1 and add four fields to table 1 from table 2 where field a&B = field A&b from table 1 and table 2 respectively

MasterAuM:

LOAD "aum_id",

"aum_bbtick" as %Key_AuMStatic,

"aum_isin",

"FUND_TOTAL_ASSETS",

"FUND_TOTAL_ASSETS_CRNCY",

"FUND_TOTAL_ASSETS_EUR",

"mastproav_Impid",

"FUND_TOTAL_ASSETS_DT" as %AumDate,

Month("FUND_TOTAL_ASSETS_DT") as Month,

Year("FUND_TOTAL_ASSETS_DT") as Year,

Week("FUND_TOTAL_ASSETS_DT") as Week;

FUND_NET_ASSET_VAL as NAV,

NAV_CRNCY as NAV_Curr,

FUND_NET_ASSET_VAL_EUR as NAV_EUR,

SQL SELECT *

FROM AUM.dbo.masterprodaumvol;

Load BBTicker as %Key_NAVStatic,

FUND_NAV_DT as NAV_Date;

Not applicable
Author

I would do LEFT JOIN ("Table 1") LOAD

Key, FieldA,FieldB, FieldC; SQL SELECT xxxxxxx FROM tablexxxxx;

Then I would delete fields I don't need in Table 1.

Hope this will help

Not applicable
Author

brilliant now get it , thanks