Populate combinations - and match

[Hamder83]

Hi 

I have two different data sets.
1.: Customer id's -these are split in 3, since we have a client hiracy.
Accounts is the lowest level, therefore hiracy level 3.
Customer is the middel, therefore hiracy level 2.
CompanyGroup is the highest, therefore hiracy level 1.

A CompanyGroup can have multiple customers.
A customer can have multiple accounts.

So I wanna populate all combinations for which customers belong to which customer groups, and which accounts to which customers.


Then I have our agreements made with our customers (customer groups).

The grouping could be like this:
CustomerGroup (Compagny Worldwide)

1. Customer 1 (Comnagny - Europe)      
         Account 1 : Stores
         Account 2 : Wholesale 
2. Customer 2 (Comnagny - US)       
          Account 1 : Stores
          Account 2 : Wholesale
3. Customer 3 (Comnagny - ASIA)      
          Account 1 : Stores
          Account 2 : Wholesale
   

We can do agreements on all 3 levels. But we wanna look first if there is an agreement on Account level (level 3), else level 2, and then 1.

I have linked in a sample agreement, showing customer identifyer and agreement level.

The purpose is when the customers shoots on our API, with a account id - I want to be able to show which level agreement level to apply on that account id.


Any help is appriciated

Thank you

[ChrisTX]

Can you use several Join tools?

First try to join on Account, send the output J anchor to a Union tool.  Send the output L anchor to another Join tool.

Next try to join on Customer, send the output J anchor to the same Union tool.  Send the output L anchor to another Join tool.

Next try to join on CompanyGroup, send the output J anchor to the same Union tool.  The output L anchor would be "unmatched" records.

Chris

[Sebastiaandb · Accepted solution]

@Hamder83 Try the attached workflow :-). The order by makes sure you always take the lowest possible level. 

[Hamder83]

Hi @Sebastiaandb  @ChrisTX 

I think Sebastians solution is good, this links the agreement to the correct level.

What I missed is:

We have two streams we wanna handle.

1.: Customer input data - providing only a account id.

For that, I wanna list which agreement belong to this account, and on which level.

So when I have the agreements mapped to the account, I can the lookup values to see if the customer has an agreement covering what they ask for.


I've attached your solution @Sebastiaandb and made a summarize from my input data, where I need help adding the correct agreements.

Does it make sense?

[mst3k]

If you need all possible combinations of a set of values, you could use the Append tool to get the cross product of the values, and chain multiple append tools together to reach all possibilities

[Sebastiaandb]

@Hamder83 

I've looked at your altered workflow. 

What i can see is this what is happening is:

You have account info in the upper stream:

In the agreement however, there's no reference to an account number, only to a customer number. That makes it impossible to just join to a certain account without joining the account to a customer id first (that's what you also are doing in the upper stream). 

What you want to achieve (at least that's what i believe):

You want to join your account info to an agreement because there can be multiple accounts per customer. Because you can't right now you end up with multiple accounts matching on an agreement because it only matches on the customer id (level 2). 

How you can solve this:

We need something in the agreement, that if it's an agreement on level 3 (the account), it needs to have some reference to the account number. Of course this will be easy if the customer just has one account but in the case that there are multiple accounts it can basically match all of them now because it only matches on customer id. If you can find anything in the agreement that might put us on track to relating it to the account number info, we can fix it :-).