Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Mike
New Contributor

Left join - multiple arguments divided by commas

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).

4 Replies

Re: Left join - multiple arguments divided by commas

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

Re: Left join - multiple arguments divided by commas

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;
Mike
New Contributor

Re: Left join - multiple arguments divided by commas

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

Does it works with many groups?

Re: Left join - multiple arguments divided by commas

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;