3 Replies Latest reply: Jul 22, 2014 2:47 PM by Massimo Grossi RSS

    How to put 2 tables together? - challenging

      Hi,

      I have 2 tables in my data model: Accounts - the main one and Filters - the one I want to use for presenting data in a given format:

       

      Accounts:

      AccountSynteticValueSign
      201-1-7-392011001
      201-2-3-4-672011291
      202-4-3202
      148-1
      459459351

       

      Filters:

       

      Level1Level2Filter
      1.Fixed Assets1.1 Buildings201-1
      1.Fixed Assets1.2 Computers201-2
      2.Current Assets202
      3. Bank Charges459

       

      I would like to present the data in sort of the filtered format, where Output=Value * Sign for all the rows from Accounts: table matching the Filter from Filters: table

      Output:

       

      Level1Level2FilterOutput
      1.Fixed Assets1.1 Buildings201100*1 + 129 *1
      1.Fixed Assets1.2 Computers201-2129 * 1
      2.Current Assets202148*(-1)
      3. Bank charges45935*1

       

      Challenges:

      1. The Output table can be several hundreds row long and changing from one month to another as new accounts are added - in my opinion it prevents use of Set Analysis in the Output: chart itself (I may be wrong here, but it's not something I can do manually)
      2. Filters are manual, thus they can have different length.
        1. The account can be 461-1, 461-2 and filter can be 461 or
        2. The account can be 203-1-5, 204-1-5 and the filter can be ???-1-5
      3. My Accounts: table is the core of the data model. Filters table is a manual inline table.

       

      Question:

      How can I create a connection key to put those 2 tables in relation?

       

      Any help would be appreciated

      Ki