How to put 2 tables together? - challenging

[#1]

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:

Account	Syntetic	Value	Sign
201-1-7-39	201	100	1
201-2-3-4-67	201	129	1
202-4-3	202	148	-1
459	459	35	1

Filters:

Level1	Level2	Filter
1.Fixed Assets	1.1 Buildings	201-1
1.Fixed Assets	1.2 Computers	201-2
2.Current Assets		202
3. Bank Charges		459

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:

Level1	Level2	Filter	Output
1.Fixed Assets	1.1 Buildings	201	100*1 + 129 *1
1.Fixed Assets	1.2 Computers	201-2	129 * 1
2.Current Assets		202	148*(-1)
3. Bank charges		459	35*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

[#2]

Hi Ki Cso, the simple solution is create Link table for this table and Link table must have the all the Combinations against Filter & Account Filter.

[#3]

Please find the attached file as reference.

[maxgro · Accepted solution]

PFA