Joining two tables based on 2 conditions

[Masond3]

HI Forum,

Probably the easiest question in the forum today.

I would like to join "Table 1" to "Table 2" where the "P_ID" in Table 1 matches the Account_SIV_Id__c in table 2 and the Level = Company. Once the join is established i would like to return the "ID" in "Table 2"

Table 1 : 

P_ID

18008075

18005885

18000837

18011726

18012912

Table 2 :

Account_SIV_Id__c	Level	Id
L5_AUS_18008075	Office	11111a
L5_AUS_18005885	Office	22222a
L5_AUS_18000837	Office	33333a
L5_AUS_18011726	Office	44444a
L5_AUS_18012912	Office	55555a
AUS_18008075	Company	11111
AUS_18005885	Company	22222
AUS_18000837	Company	33333
AUS_18011726	Company	44444
AUS_18012912	Company	55555

Expected Outcome 

P_ID	Level	Id
18008075	Company	11111
18005885	Company	22222
18000837	Company	33333
18011726	Company	44444
18012912	Company	55555

looking forward to your help

Regards
Masond

[Luke_C]

Hi @Masond3 

Here's one approach:

1. Filter the data to Company Level
2. Use a formula to get the ID from the Account_SIV_Id. I used the substring method for this to return the numbers after the underscore. You could also use regex but I wanted to keep this simple.
3. Join based on ID