Filter 1 Dimension in 2 Columns via key

[petergassert]

Hi,

i am looking for a best practice.
If i got 2 fields in a table with the same data, am I able to build up a filter without replicating the data.

Example:
Table 1 includes interactions between 2 companies. The IDs are given in 2 columns.

Id	Company ID1	Company ID2	CompPair	Amount
1	A	B	A-B	10
2	B	C	B-C	20
3	B	A	A-B	20

Table 2 includes Company Details, that will be needed for further filtering. One filter could by directly by company name and one by Grouping.  

CompanyID	Name	Group
A	Test 1 AG	Grouping 1
B	Test 2	Grouping 1
C	Test 3 Ltd	Grouping 2

Do i always need to duplicate the data in table 2 to connect to CompanyID 2 as well, or is there another possibility how to build up the filtering in a  nice performant way? 

In this case, Amount needs to be summarized. 

The filtering in the frontend needs to be via "CompanyID 1",  "Company ID2" or even the field Group.

My expectation is that the interaction of the companies will be displayed:

Filter 1	Filter 2	Sum(Amount)
Name= Test 1	Name=Test 2	30
Group = Grouping 1	Name=Test 3	20

Aim is that you have 2 companies, and you want to see, filter all transactions between with them.

I could not see, how i am able to do this in your example with only one company column.

Thanks for joining my challange.

[JustinDallas]

You didn't post what your desired data would look like.  But generally, in cases like this, I use a join table.

I have an example below.

Details:
LOAD CompanyID as '%company_key',
*
;
LOAD * INLINE
[
CompanyID,	Name,	Group
A,Test 1 AG,Grouping 1
B,Test 2 ,Grouping 1
C,Test 3 Ltd,Grouping 2
]
;

Interactions:
LOAD Id as '%interaction_key',
*
;
LOAD * INLINE 
[
Id,	Company ID1,	Company ID2, CompPair,	Amount
1,	A,	B,	A-B,	10
2,	B,	C,	B-C,	20
3,	B,	A,	A-B,	20
]
;

InteractionDetailJoin:
LOAD 
%interaction_key,
"Company ID1" AS '%company_key',
'Company 1' AS 'JoinType'
RESIDENT Interactions
;

Concatenate(InteractionDetailJoin)
LOAD 
%interaction_key,
"Company ID2" AS '%company_key',
'Company 2' AS 'JoinType'
RESIDENT Interactions
;

EXIT Script
;

Let me know if the resulting table is what you are looking for.

[petergassert]

Hi Justin,

Thanks for this fast reply. For sure it would be beneficial to tell you the expected result.

In this case, Amount needs to be summarized. 

The filtering in the frontend needs to be via "CompanyID 1",  "Company ID2" or even the field Group.

My expectation is that the interaction of the companies will be displayed:

Filter 1	Filter 2	Sum(Amount)
Name= Test 1	Name=Test 2	30
Group = Grouping 1	Name=Test 3	20

Aim is that you have 2 companies, and you want to see, filter all transactions between with them.

I could not see, how i am able to do this in your example with only one company column.

Thanks for your help