topic Assign random person from one table to another in QlikView

Assign random person from one table to another

QlikMo — Fri, 21 Apr 2023 12:26:29 GMT

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

Re: Assign random person from one table to another

QlikMo — Mon, 24 Apr 2023 12:40:18 GMT

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.

Re: Assign random person from one table to another

Kushal_Chawda — Mon, 24 Apr 2023 12:48:50 GMT

@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
];

Re: Assign random person from one table to another

QlikMo — Mon, 24 Apr 2023 13:13:50 GMT

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

Re: Assign random person from one table to another

Kushal_Chawda — Mon, 24 Apr 2023 14:21:13 GMT

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

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;

Re: Assign random person from one table to another

QlikMo — Mon, 24 Apr 2023 15:14:13 GMT

Thank you @Kushal_Chawda you legend!

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