topic Re: Pick() random order in QlikView

Pick() random order

Tue, 11 Mar 2014 09:57:35 GMT

Hi,

I need to make a Pick() statement, where the order of the expressions is random every time you reload.

Example:

Load

RecNo() as DoorNo,

Pick(RecNo(),'Car','Goat','Goat') as DoorPrize,

AutoGenerate 3,

DoorNo & '|' & DoorPrize as Door;

Thanks for your help in advance,

Lucas

My actual script:

Load *,

DoorNo & '|' & DoorPrize as Door;

Load

RecNo() as DoorNo,

Pick(RecNo(),'Car','Goat','Goat') as DoorPrize

AutoGenerate 3;

Re: Pick() random order

tresB — Tue, 11 Mar 2014 10:05:32 GMT

May be using Rand() in your DoorNo expression

Re: Pick() random order

Tue, 11 Mar 2014 10:09:23 GMT

Re: Pick() random order

MK_QSL — Tue, 11 Mar 2014 10:21:29 GMT

You can try something like below..

Load *,

DoorNo & '|' & DoorPrize as Door;

Load

Pick(Ceil(3*Rand()),1,2,3) as DoorNo,

Pick(Ceil(3*Rand()),'Car','Goat','Goat') as DoorPrize,

Ceil(Rand()*3) as Random

AutoGenerate 3;

Re: Re: Pick() random order

Tue, 11 Mar 2014 10:25:15 GMT

This gives me (pic), whereas I want 1|(Car or Goat) 2|(Car or Goat) 3|(Car or Goat), where Car appears 1 time and Goat 2 times.

Re: Pick() random order

amit_saini — Tue, 11 Mar 2014 10:31:04 GMT

Hi Lucas,

This example may help you:

Syntax:

Month:

load * inline [

;

MonthFinal:

LOAD *,

floor(RowNo() * 1) AS Rank

Resident Month;

DROP Table Month;

Output:

Month	Rank
Dec 12	1
Nov 12	2
Jul 12	3
Jun 12	4
May 12	5
Apr 12	6
Mar 12	7
Feb 12	8
Jan 12	9

Thanks,

Re: Re: Pick() random order

Tue, 11 Mar 2014 10:38:12 GMT

Thanks for the answer, but I need to know how to sort the expression (Rank in your example) randomly, so it might come out like this:

Re: Pick() random order

tresB — Tue, 11 Mar 2014 11:29:39 GMT

Try below code:

Tab1:
Load

     RecNo() As DoorNo,
     Pick(RecNo(),'Car','Goat','Goat') as DoorPrize
     AutoGenerate 3;

Tab2:
Load
       Concat(DoorNo, ';', Rand()) as DoorNo2               // This one is the trick
Resident Tab1;

Let vDoors=Peek('DoorNo2');

Tab3:
Load
SubField('$(vDoors)', ';', RecNo()) & '|' & DoorPrize as Door
Resident Tab1;

Drop Tables Tab1, Tab2;

It's working.

Re: Pick() random order

Tue, 11 Mar 2014 11:30:02 GMT

Hi Lucas,

find here my suggestion:

Mappi:

Mapping LOAD * Inline [

FA, FB

1, Car

2, Goat

3, Goat

];

Load *,

DoorNo & '|' & DoorPrize as Door;

Load

RecNo() as DoorNo,

ApplyMap('Mappi', Ceil(Rand()*10 /4)) as DoorPrize

AutoGenerate 3 ;

Regards

Roland

Re: Pick() random order

Tue, 11 Mar 2014 11:43:14 GMT

Thanks for the reply,

Greetings,

Lucas

Re: Pick() random order

rbecher — Tue, 11 Mar 2014 11:48:58 GMT

Hi Lucas,

probably this works (if I understand it right):

StartValue:
Load
     Ceil(Rand()*3) as Random
AutoGenerate 1;

Shuffle:
Load *,
     DoorNo & '|' & DoorPrize as Door;
Load
     RecNo() as DoorNo,
     Pick(Mod(Peek('Random', 0, 'StartValue')+RecNo(),3)+1,'Car','Goat','Goat') as DoorPrize
AutoGenerate 3;

- Ralf

Re: Pick() random order

Tue, 11 Mar 2014 12:04:35 GMT

Hi,

This works! So it's pretty much like Pick((Ceil(Rand()*3)+RecNo())*3,'Car','Goat','Goat') as DoorPrize ?

Thanks for your time!

Lucas

Re: Pick() random order

Tue, 11 Mar 2014 12:08:38 GMT

It works! Could you explain to me what exactly the SubField function does?

Thanks for helping me!

Lucas

Re: Pick() random order

tresB — Tue, 11 Mar 2014 12:11:30 GMT

subfield(s, 'delimiter' [ , index ] )

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. index is an optional integer denoting which of the substrings should be returned.

Re: Pick() random order

rbecher — Tue, 11 Mar 2014 12:17:17 GMT

No, you cannot be sure to catch all if you call rand() more than once..