4 Replies Latest reply: Nov 12, 2009 12:17 PM by jking1975 RSS

    Simple joins

    jking1975

      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 de Arce

          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
              jking1975

              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;