Left join - multiple arguments divided by commas

[Anonymous]

HI, I've got problem with mapping users to groups. There are following users in groups:

Chandler - Group X
Andrew - Group X
Mike - Group Y
Darius - Group Y
Margaret - Group Y

And there are following processes with users divided with commas:

1. Mike, Andrew, Chandler (expected value: Group X)
2. Chandler, Mike, Darius, Margaret (expected value: Group Y)
3. Margaret, Andrew, Mike (expected value: Group Y)
4. Andrew, Mike (expected value: Group X)

I need a script to check which processes should be mapped to correct groups (checking which group has majority in given process).

[sunny_talwar]

For number 4, how is the expected output Group X... there is one member from Group X and one number from Group Y?

[sunny_talwar]

This script works as long as there is no tie between groups

MappingTable:
Mapping
LOAD * INLINE [
    Name, Group
    Chandler, Group X
    Andrew, Group X
    Mike, Group Y
    Darius, Group Y
    Margaret, Group Y
];

Team:
LOAD TeamMembers,
	 Group,
     Count(Group) as Count
Group By TeamMembers, Group;
LOAD TeamMembers,
	 SubField(Group, ', ') as Group;
LOAD *,
	 MapSubString('MappingTable', TeamMembers) as Group;
LOAD * INLINE [
    TeamMembers
    "Mike, Andrew, Chandler"
    "Chandler, Mike, Darius, Margaret"
    "Margaret, Andrew, Mike"
    "Andrew, Mike"
];

Right Join (Team)
LOAD TeamMembers,
     Max(Count) as Count
Resident Team
Group By TeamMembers;

[Anonymous]

If there is a tie I need any of tied group.

Does it works with many groups?

[sunny_talwar]

It should work with multiple groups.... try this to randomly choose a group if there is a tie

MappingTable:
Mapping
LOAD * INLINE [
    Name, Group
    Chandler, Group X
    Andrew, Group X
    Mike, Group Y
    Darius, Group Y
    Margaret, Group Y
];

Team:
LOAD TeamMembers,
	 Group,
     Count(Group) + RowNo()/100000 as Count
Group By TeamMembers, Group;
LOAD TeamMembers,
	 SubField(Group, ', ') as Group;
LOAD *,
	 MapSubString('MappingTable', TeamMembers) as Group;
LOAD * INLINE [
    TeamMembers
    "Mike, Andrew, Chandler"
    "Chandler, Mike, Darius, Margaret"
    "Margaret, Andrew, Mike"
    "Andrew, Mike"
];

Right Join (Team)
LOAD TeamMembers,
     Max(Count) as Count
Resident Team
Group By TeamMembers;