6 Replies Latest reply: Sep 27, 2010 1:09 PM by Miguel Angel Baeyens de Arce RSS

    HOW TO APPEND TWO TABLES THAT SHARE TWO FIELDS

    ANGUYEN100

       

      Hi

      Im a beginner when it comes to script lanaguage on Qlikview. I have two tables, MasterTurnobver & Turnoverbloomberg.

      Both tables share two common fields key_turnoverstatic and turnover date

       

      In access I can easily append these fields, but how can i do that in Qlikview? Evenutally I will need to consolidate this table so I can link it to a reference table...

       

       

      MasterTurnover:
      SQL SELECT "msamk_id",
      "msamk_ticker" as Key_Turnoverstatic,
      "msamk_isin",
      "msamk_dateimported",
      "msamk_startdate" as TURNOVER_DATE,
      "msamk_enddate",
      "msamk_broker",
      "msamk_currency",
      "msamk_srcname",
      "msamk_markitname",
      "msamk_turnoverbroker",
      "msamk_turnovertotal",
      "msamk_brokerpercent",
      "msamk_brokerrank",
      "msamk_1st",
      "msamk_2nd",
      "msamk_3rd",
      "SSMA_TimeStamp"
      FROM msamarkit.dbo.msamarkit;

      CONCATENATE ("MasterTurnover")

      Turnoverbloomberg:
      SQL SELECT "mastproav_id",
      BBTicker as Key_Turnoverstatic,
      "FUND_TOTAL_ASSETS",
      "FUND_TOTAL_ASSETS_CRNCY",
      "FUND_TOTAL_ASSETS_EUR",
      "EQY_TURNOVER",
      CRNCY,
      "EQY_TURNOVER_EUR",
      "PX_LAST",
      "mastproav_Impid",
      "mastproav_date" as TURNOVER_DATE,
      "FUND_TOTAL_ASSETS_DT",
      "FUND_NET_ASSET_VAL",
      "NAV_CRNCY",
      "FUND_NET_ASSET_VAL_EUR",
      "FUND_NAV_DT",
      "EQY_SH_OUT"
      FROM AUM.dbo.masterprodaumvol;

       

       

       

       

        • HOW TO APPEND TWO TABLES THAT SHARE TWO FIELDS
          Miguel Angel Baeyens de Arce

          Hi,

          Just as you are doing, it seems fine to me, although I always prefer to use "preceding load" statements, to distinguish every moment which fields come from the data base and which ones I'm actually loading into QlikView.

          Concatenating both tables will append the contents of one to the other, so there will be no joins or data reduction, which may be useful for you. In your sample code, there's no need for label "Turnoverbloomberg:" as for all purposes, the result will be one table.

          Regards.

            • HOW TO APPEND TWO TABLES THAT SHARE TWO FIELDS
              ANGUYEN100

              Hi what do you mean by preceding load statements? I thought I was loading everything from the database and everytime I need to refresh the table I need to reload the data from sequel server

              Also after concatenating the table I need to formate the turnover date field

              Is it correct to do this after the concatenation

               

              Table1:
              LOAD

              *,
              Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
              year(TURNOVER_DATE) as Tyear

              Resident MasterTurnover