4 Replies Latest reply: Feb 12, 2015 12:44 PM by Eveline van den Heuvel RSS

    Creating a new column out of columns from two tables (based on a date condition)

      Hi all, I'm trying to solve the case below, but I have trouble finding the answer. I really hope someone could help me solving it.

       

      Each ticker is traded on a trade date, the traded amount is called Market Value.

      For each traded ticker, I'm trying to create a new column Market Value (MV) divided by the Net Asset Value (NAV).

       

      The NAV in this column should be the NAV (from NAVTable) that belongs to the account the ticker is in and the date Trade Date and NAV Date should be equal.

       

      The tables below are the ones that I have.

       

      TradeTable

      AccountTickerMarket ValueTrade Date
      ClipperABC406 Jan 2015
      ClipperDEF506 Jan 2015

      Blender

      GHI208 Jan 2015
      BlenderJKL309 Jan 2015
      LindtABC1007 Jan 2015
      LindtMNO508 Jan 2015

       

      NAVTable

      NAV DateClipperBlenderLindt
      5 Jan 2015100020201500
      6 Jan 2015101020251500
      7 Jan 2015101525001505
      8 Jan 2015102025101510
      9 Jan 2015101025051490

       

      My answer should be something like an additional column in TradeTable called "MV/NAV, the answers being

      3.96% (=40/1010)

      4.95% (=50/1010)

      0.80% (=20/2510)

      1.20% (=30/2505)

      6.64% (=100/1505)

      3.31% (=50/1510)


      Code that I have in my Data Load Editor so far:

       

      TradeTable:
      Load
          "Account"

          "Ticker"

          "Market Value",
          "Trade Date"

      FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet1);


      NAVTable:

      CrossTable(Account,NAV,1)

      Load
          "NAV Date",
          "Clipper",

          "Blender",

          "Lindt"

      FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet2);


        • Re: Creating a new column out of columns from two tables (based on a date condition)
          Brian Booden

          Try this in the Data Load Editor:

           

          TradeTable:
          Load
              "Account"

              "Ticker"

              "Market Value",
              "Trade Date"

          FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet1);

           

          NAVTable:

          CrossTable(Account, NAV)

          LOAD

              "NAV Date" as "Trade Date",

              Clipper,

              Blender,

              Lindt

          FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet2);

           

          The link between your 2 tables should then be Trade Date and Account.

           

          Then, in your expression / measure on your chart, to create your calculation:

           

          Sum([Market Value] / [NAV])

           

          Set Number Formatting on the expression to Number, Simple and format 12.34%.

           

          That should be it!

           

          Regards,

          Brian