topic Simple joins in QlikView

Simple joins

Thu, 12 Nov 2009 15:32:54 GMT

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

Simple joins

Miguel_Angel_Baeyens — Thu, 12 Nov 2009 15:47:46 GMT

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.

Simple joins

Thu, 12 Nov 2009 16:41:49 GMT

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;

Simple joins

Thu, 12 Nov 2009 16:49:29 GMT

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

Simple joins

Thu, 12 Nov 2009 17:17:35 GMT

brilliant now get it , thanks