Wildmatching a value in a different column

[waleeed_mahmood]

Hi everyone,

I have spent 3 days on the following problem but not enough luck in finding the correct answer.  I have the following table as my source table. 

TABLE:

Opps	Owner	Value	Team
A1	Tony	2500	Tony;Bob;Helen
A2	Tony	10000	Bob,George,Tony
A3	Tony	12000	Helen;Tony
A4	Tony	45000	Tony
A5	Tony	30000	Tony
A6	Tony	30000	George;Tony
A7	Tony	50000	Tony
A12	Steve	12000	Tony;Mike;Balao;Helen
A13	Miara	45000	George;Mike;Helen
A15	Bob	30000	Bob,George,Tony
A16	Tony	50000	Sunny;Tony
A17	Sunny	30000	George;Mike;Helen
A18	Tony	30000

What i want to do is the following:

1: I want to do set analysis on the above table and make variables vTeam and vRep.

2: for vTeam, I want to sum values where a person exists in the Team column (NOT ON ITS OWN BUT IN A GROUP) but not in the Owner column. So for example if we took Tony as an example, his resulting table would like this and i only want the sum (in RED) value and not the table itself.

Opps	Owner	Value	Team
A12	Steve	12000	Tony;Mike;Balao;Helen
A15	Bob	30000	Bob,George,Tony
		42000

3: For vRep, I want to sum values where a person exists in the Owner column and in the Team column so for example if we took Tony again, his table would look like the following, i only want the sum and not the actual table:

Opps	Owner	Value	Team
A1	Tony	2500	Tony;Bob;Helen
A2	Tony	10000	Bob,George,Tony
A3	Tony	12000	Helen;Tony
A4	Tony	45000	Tony
A5	Tony	30000	Tony
A6	Tony	30000	George;Tony
A7	Tony	50000	Tony
A16	Tony	50000	Sunny;Tony
A18	Tony	30000
		259500

UPDATE:

This is the results im looking for :

So, if i select tony, the value should sum up (where he exists in Owner and Team) 

Final Table:
Tony	301500
Steve	12000
Miara	45000
Bob	42500
Sunny	80000

Side Note:

The reason i want this to be a set analysis is because i have this information in a pivot table along with targets and actuals. So, if i select tony from there, target and actuals show fine. Just the opps value is wrong. I was hoping to sum vRep+ vTeam to get the total value in that table.

Please let me know if you need more information.

Thank you

@sunny_talwar 

[Saravanan_Desingh]

A3 and A6 in the vRep table are incorrect. Can you check?

[waleeed_mahmood]

Hi,

Yes, it has been corrected. The main table was changed to represent the correct results.

Thanks

[Saravanan_Desingh]

One solution in Script:

tab1:
LOAD *, SubField(Team_Name,';') As vTeam
;
LOAD *, Replace(Team, Owner, '') As Team_Name
	  , If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
;	  
LOAD RecNo() As RecID, * INLINE [
    Opps, Owner, Value, Team
    A1, Tony, 2500, Tony;Bob;Helen
    A2, Tony, 10000, Bob;George;Tony
    A3, Tony, 12000, Helen;Tony
    A4, Tony, 45000, Tony
    A5, Tony, 30000, Tony
    A6, Tony, 30000, George;Tony
    A7, Tony, 50000, Tony
    A12, Steve, 12000, Tony;Mike;Balao;Helen
    A13, Miara, 45000, George;Mike;Helen
    A15, Bob, 30000, Bob;George;Tony
    A16, Tony, 50000, Sunny;Tony
    A17, Sunny, 30000, George;Mike;Helen
    A18, Tony, 30000, 
];

[Saravanan_Desingh]

Output

[waleeed_mahmood]

Hi,

Thank you for your time. 

This is what im looking for as a result 

Final Table:
Owner	Total Value
Tony	301500
Steve	12000
Miara	45000
Bob	42500
Sunny	80000

I want to have one field called owner with all the names in my main table. Then, when i select one person, it should sum both vTeam and vRep and give one final value. So, in case of Tony, it should be 42000+259500 = 301500.

Thanks

[Saravanan_Desingh · Accepted solution]

Check this.

tab1:
LOAD *, If(SubField(Team_Name,';')<>Owner, SubField(Team_Name,';')) As vTeam
;
LOAD *, Replace(Team, Owner, '') As Team_Name
	  , If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep
;	  
LOAD RecNo() As RecID, * INLINE [
    Opps, Owner, Value, Team
    A1, Tony, 2500, Tony;Bob;Helen
    A2, Tony, 10000, Bob;George;Tony
    A3, Tony, 12000, Helen;Tony
    A4, Tony, 45000, Tony
    A5, Tony, 30000, Tony
    A6, Tony, 30000, George;Tony
    A7, Tony, 50000, Tony
    A12, Steve, 12000, Tony;Mike;Balao;Helen
    A13, Miara, 45000, George;Mike;Helen
    A15, Bob, 30000, Bob;George;Tony
    A16, Tony, 50000, Sunny;Tony
    A17, Sunny, 30000, George;Mike;Helen
    A18, Tony, 30000, 
];

tab2:
LOAD DISTINCT vTeam As Owner, Value As Team_Value, Opps As Team_Opps
Resident tab1;
LOAD DISTINCT vRep As Owner, Value As Rep_Value, Opps As Rep_Opps
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

[waleeed_mahmood]

Hi ,

Thank you for replying so quickly.

You solution is almost correct. Some mistakes:

1: Sunny is missing 30000 from his Values.

2: its missing values for Miara and Steve completely. 

[waleeed_mahmood]

Hello, 

the issue is solved if you replace the following:

If(Len(Team)=0 Or Index(Team,Owner), Owner) As vRep

with :

Owner As vRep

Thanks