Assign random person from one table to another

[QlikMo]

Hello

I have a table with dimensions Line and Bay. A line contains multiple bays. 

I also have a list of people tied to a line. 

In the load script I need to randomly assign people to bays in their respective line. The amount of people in the random persons list matches the bays, so every named person  should be assigned their own bay.   Sometimes a person may appear twice, in this scenario they should be assigned two bays for that line.

A sample of the expected output would be 

Line	Bay	Person
Line 1	Bay 1	Emil Ho
Line 1	Bay 2	Abel Luna
Line 1	Bay 3	Oscar Ward

BaysAndLine:
LOAD * INLINE [
    Line, Bay
    Line 1, Bay 1
    Line 1, Bay 2
    Line 1, Bay 3
    Line 1, Bay 4
    Line 1, Bay 5
    Line 1, Bay 6
    Line 1, Bay 7
    Line 1, Bay 8
    Line 1, Bay 9
    Line 1, Bay 10
    Line 1, Bay 11
    Line 1, Bay 12
    Line 1, Bay 13
    Line 1, Bay 14
    Line 1, Bay 15
    Line 1, Bay 16
    Line 1, Bay 17
    Line 1, Bay 18
    Line 1, Bay 19
    Line 1, Bay 20
    Line 1, Bay 21
    Line 1, Bay 22
    Line 1, Bay 23
    Line 1, Bay 24
    Line 1, Bay 25
    Line 1, Bay 26
    Line 2, Bay 1
    Line 2, Bay 2
    Line 2, Bay 3
    Line 2, Bay 4
    Line 2, Bay 5
    Line 2, Bay 6
    Line 2, Bay 7
    Line 2, Bay 8
    Line 2, Bay 9
    Line 2, Bay 10
    Line 2, Bay 11
    Line 2, Bay 12
    Line 2, Bay 13
    Line 2, Bay 14
    Line 2, Bay 15
    Line 2, Bay 16
    Line 2, Bay 17
    Line 2, Bay 18
    Line 2, Bay 19
    Line 2, Bay 20
    Line 2, Bay 21
    Line 2, Bay 22
    Line 2, Bay 23
    Line 2, Bay 24
    Line 2, Bay 25
    Line 2, Bay 26
];



LineAndPerson:
LOAD * INLINE [
    Line, Person
    Line 1, Emelia Bolton
    Line 1, Libbie Fuentes
    Line 1, Chantelle Horne
    Line 1, Adele Taylor
    Line 1, Samuel Cooke
    Line 1, Anna Burgess
    Line 1, Emil Ho
    Line 1, Lachlan Ingram
    Line 1, Amaan Mccormick
    Line 1, Rufus Mckay
    Line 1, Kobi Case
    Line 1, Abel Luna
    Line 1, Abel Luna
    Line 1, Katie Gray
    Line 1, Marianne Thomas
    Line 1, Herbie Kramer
    Line 1, Bridie Pace
    Line 1, Oscar Ward
    Line 1, Oscar Ward
    Line 1, Celine Irwin
    Line 1, Krish Beck
    Line 1, Zainab Patterson
    Line 1, Marwa Harrell
    Line 1, Esha Kirk
    Line 1, Mahdi Huff
    Line 1, Wayne Jean
    Line 2, Haaris Barnes
    Line 2, Yusuf Morris
    Line 2, Isla Huff
    Line 2, Sebastian Whitaker
    Line 2, Enzo Kent
    Line 2, Eden Banks
    Line 2, Jeffrey Underwood
    Line 2, Marilyn Haas
    Line 2, Darcy King
    Line 2, Bertie Matthams
    Line 2, Paula Horn
    Line 2, Gabrielle Lucero
    Line 2, Amira Mcguire
    Line 2, Mary Roman
    Line 2, Jenson Palmer
    Line 2, Jenson Palmer
    Line 2, Herbert Frank
    Line 2, Mikolaj Mckenzie
    Line 2, Kate Rich
    Line 2, Jose Ramsey
    Line 2, "Melody O'Quinn"
    Line 2, Jessie Brooks
    Line 2, Aryan Rubio
    Line 2, Theodore Brennan
    Line 2, Constance Faulkner
    Line 2, Derek Dunlap
];

I have never done this before and would appreciate if someone could point me in the right direction.

Thanks

[QlikMo]

I've tried to adapt a script from the last post on this thread https://community.qlik.com/t5/QlikView-App-Dev/Random-numbers-without-repeating/m-p/127315

As you can see (on the left) the number strings start going funny (not sure why)

TEAMS:
Load 
RecNo() as Person#,
Person

resident LineandPerson where Line='Line 1';


SIZE:
	LOAD Count(Person) AS size RESIDENT TEAMS;
	size = Peek('size');
	DROP TABLE SIZE;
	
ALLTEAMS: 
	LOAD Concat(Person#,'|') AS AllTeams RESIDENT TEAMS;
	AllTeams = Peek('AllTeams')&'|';
	DROP TABLE ALLTEAMS;	
	

PICKS:
	LOAD
	  RecNo() AS Pick#,
	  Replace( If(RecNo()=1, '$(AllTeams)', Peek('Remaining')), 
	  	SubField(If(RecNo()=1, '$(AllTeams)', Peek('Remaining')),'|',RandomPick) & '|','') AS Remaining,
	  SubField(If(RecNo()=1, '$(AllTeams)', Peek('Remaining')),'|',RandomPick) AS Person#,
	  RandomPick;
	LOAD
	  Ceil(Rand()*($(size)-(RecNo()-1))) AS RandomPick
	AUTOGENERATE $(size);

size=;
AllTeams=;	

I've also tried to give each row a random number and do a join whilst sorting. This didn't work and gave me the same results after each reload.

Any help is appreciated. 

[Kushal_Chawda]

@QlikMo  try below. As you need to assign person randomly I have not perform any sort on data. In case if you want assignment based on alphabetical order of name, first you need to sort your data then apply autonumber and join with table 

BaysAndLine:
LOAD *, AutoNumber(RecNo(),Line) as Rank
INLINE [
Line, Bay
Line 1, Bay 1
Line 1, Bay 2
Line 1, Bay 3
Line 1, Bay 4
Line 1, Bay 5
Line 1, Bay 6
Line 1, Bay 7
Line 1, Bay 8
Line 1, Bay 9
Line 1, Bay 10
Line 1, Bay 11
Line 1, Bay 12
Line 1, Bay 13
Line 1, Bay 14
Line 1, Bay 15
Line 1, Bay 16
Line 1, Bay 17
Line 1, Bay 18
Line 1, Bay 19
Line 1, Bay 20
Line 1, Bay 21
Line 1, Bay 22
Line 1, Bay 23
Line 1, Bay 24
Line 1, Bay 25
Line 1, Bay 26
Line 2, Bay 1
Line 2, Bay 2
Line 2, Bay 3
Line 2, Bay 4
Line 2, Bay 5
Line 2, Bay 6
Line 2, Bay 7
Line 2, Bay 8
Line 2, Bay 9
Line 2, Bay 10
Line 2, Bay 11
Line 2, Bay 12
Line 2, Bay 13
Line 2, Bay 14
Line 2, Bay 15
Line 2, Bay 16
Line 2, Bay 17
Line 2, Bay 18
Line 2, Bay 19
Line 2, Bay 20
Line 2, Bay 21
Line 2, Bay 22
Line 2, Bay 23
Line 2, Bay 24
Line 2, Bay 25
Line 2, Bay 26
];

Join (BaysAndLine)
LOAD *,AutoNumber(RecNo(),Line) as Rank
INLINE [
Line, Person
Line 1, Emelia Bolton
Line 1, Libbie Fuentes
Line 1, Chantelle Horne
Line 1, Adele Taylor
Line 1, Samuel Cooke
Line 1, Anna Burgess
Line 1, Emil Ho
Line 1, Lachlan Ingram
Line 1, Amaan Mccormick
Line 1, Rufus Mckay
Line 1, Kobi Case
Line 1, Abel Luna
Line 1, Abel Luna
Line 1, Katie Gray
Line 1, Marianne Thomas
Line 1, Herbie Kramer
Line 1, Bridie Pace
Line 1, Oscar Ward
Line 1, Oscar Ward
Line 1, Celine Irwin
Line 1, Krish Beck
Line 1, Zainab Patterson
Line 1, Marwa Harrell
Line 1, Esha Kirk
Line 1, Mahdi Huff
Line 1, Wayne Jean
Line 2, Haaris Barnes
Line 2, Yusuf Morris
Line 2, Isla Huff
Line 2, Sebastian Whitaker
Line 2, Enzo Kent
Line 2, Eden Banks
Line 2, Jeffrey Underwood
Line 2, Marilyn Haas
Line 2, Darcy King
Line 2, Bertie Matthams
Line 2, Paula Horn
Line 2, Gabrielle Lucero
Line 2, Amira Mcguire
Line 2, Mary Roman
Line 2, Jenson Palmer
Line 2, Jenson Palmer
Line 2, Herbert Frank
Line 2, Mikolaj Mckenzie
Line 2, Kate Rich
Line 2, Jose Ramsey
Line 2, "Melody O'Quinn"
Line 2, Jessie Brooks
Line 2, Aryan Rubio
Line 2, Theodore Brennan
Line 2, Constance Faulkner
Line 2, Derek Dunlap
];

[QlikMo]

Hi @Kushal_Chawda 

Many thanks for your reply. Your script does work but the random assignments stay the same after each reload. I need a way to make them random after each reload (Sorry I realise I wasn't clear about this point in my original post)

Thanks

[Kushal_Chawda · Accepted solution]

@QlikMo  just to make sure that Random number not repeating, try to multiply actual record count with random number like below

BaysAndLine:
LOAD *, AutoNumber(RecNo(),Line) as Rank
INLINE [
Line, Bay
Line 1, Bay 1
Line 1, Bay 2
Line 1, Bay 3
Line 1, Bay 4
Line 1, Bay 5
Line 1, Bay 6
Line 1, Bay 7
Line 1, Bay 8
Line 1, Bay 9
Line 1, Bay 10
Line 1, Bay 11
Line 1, Bay 12
Line 1, Bay 13
Line 1, Bay 14
Line 1, Bay 15
Line 1, Bay 16
Line 1, Bay 17
Line 1, Bay 18
Line 1, Bay 19
Line 1, Bay 20
Line 1, Bay 21
Line 1, Bay 22
Line 1, Bay 23
Line 1, Bay 24
Line 1, Bay 25
Line 1, Bay 26
Line 2, Bay 1
Line 2, Bay 2
Line 2, Bay 3
Line 2, Bay 4
Line 2, Bay 5
Line 2, Bay 6
Line 2, Bay 7
Line 2, Bay 8
Line 2, Bay 9
Line 2, Bay 10
Line 2, Bay 11
Line 2, Bay 12
Line 2, Bay 13
Line 2, Bay 14
Line 2, Bay 15
Line 2, Bay 16
Line 2, Bay 17
Line 2, Bay 18
Line 2, Bay 19
Line 2, Bay 20
Line 2, Bay 21
Line 2, Bay 22
Line 2, Bay 23
Line 2, Bay 24
Line 2, Bay 25
Line 2, Bay 26
];

let vRecords = NoOfRows('BaysAndLine');

LineAndPerson:
LOAD *,round(Rand()*(($(vRecords)*10)-1)+1) as Random_Number
INLINE [
Line, Person
Line 1, Emelia Bolton
Line 1, Libbie Fuentes
Line 1, Chantelle Horne
Line 1, Adele Taylor
Line 1, Samuel Cooke
Line 1, Anna Burgess
Line 1, Emil Ho
Line 1, Lachlan Ingram
Line 1, Amaan Mccormick
Line 1, Rufus Mckay
Line 1, Kobi Case
Line 1, Abel Luna
Line 1, Abel Luna
Line 1, Katie Gray
Line 1, Marianne Thomas
Line 1, Herbie Kramer
Line 1, Bridie Pace
Line 1, Oscar Ward
Line 1, Oscar Ward
Line 1, Celine Irwin
Line 1, Krish Beck
Line 1, Zainab Patterson
Line 1, Marwa Harrell
Line 1, Esha Kirk
Line 1, Mahdi Huff
Line 1, Wayne Jean
Line 2, Haaris Barnes
Line 2, Yusuf Morris
Line 2, Isla Huff
Line 2, Sebastian Whitaker
Line 2, Enzo Kent
Line 2, Eden Banks
Line 2, Jeffrey Underwood
Line 2, Marilyn Haas
Line 2, Darcy King
Line 2, Bertie Matthams
Line 2, Paula Horn
Line 2, Gabrielle Lucero
Line 2, Amira Mcguire
Line 2, Mary Roman
Line 2, Jenson Palmer
Line 2, Jenson Palmer
Line 2, Herbert Frank
Line 2, Mikolaj Mckenzie
Line 2, Kate Rich
Line 2, Jose Ramsey
Line 2, "Melody O'Quinn"
Line 2, Jessie Brooks
Line 2, Aryan Rubio
Line 2, Theodore Brennan
Line 2, Constance Faulkner
Line 2, Derek Dunlap
];

Join(BaysAndLine)
Load AutoNumber(RecNo(),Line) as Rank,
Line,
Person
Resident LineAndPerson
Order by Line,Random_Number;

Drop Table LineAndPerson;

Drop Field Rank;

[QlikMo]

Thank you @Kushal_Chawda you legend! 

I've seen people multiply rand() but never fully understood why, now I do. Thanks again!