Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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;
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
brilliant now get it , thanks