Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I need a script to check which processes should be mapped to correct groups (checking which group has majority in given process).
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;
If there is a tie I need any of tied group.
Does it works with many groups?
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;