topic Re: Creation of Lottery Permutations via load script in QlikView

Creation of Lottery Permutations via load script

Anonymous — Mon, 12 Mar 2012 16:16:16 GMT

Hi,

So far I have the following code, but I need to incorporate the FOR NEXT Loops to then have the values passed into an inline table.

How would I pass the values of the BALL_1, 2 etc into the in-memory table?

Advice would be most appreciated.

Thanks

For Ball_1 = 1 To 44

For Ball_2 = Ball_1 + 1 To 45

For Ball_3 = Ball_2 + 1 To 46

For Ball_4 = Ball_3 + 1 To 47

For Ball_5 = Ball_4 + 1 To 48

For Ball_6 = Ball_5 + 1 To 49

Next Ball_6

Next Ball_5

Next Ball_4

Next Ball_3

Next Ball_2

Next Ball_1

Re: Creation of Lottery Permutations via load script

pover — Mon, 12 Mar 2012 16:43:44 GMT

It might be easy to do joins without matching keys that will generate all permutations between 2 tables.

Load rowno() as Ball_1
AutoGenerate 45;
Left Join
Load rowno() as Ball_2
AutoGenerate 45;
Left Join
Load rowno() as Ball_3
AutoGenerate 45;
Left Join
Load rowno() as Ball_4
AutoGenerate 45;
Left Join
Load rowno() as Ball_5
AutoGenerate 45;

Karl

Creation of Lottery Permutations via load script

swuehl — Mon, 12 Mar 2012 19:11:27 GMT

You can create the table using your for-loops like

For Ball_1 = 1 To 44

For Ball_2 = Ball_1 + 1 To 45

For Ball_3 = Ball_2 + 1 To 46

For Ball_4 = Ball_3 + 1 To 47

For Ball_5 = Ball_4 + 1 To 48

For Ball_6 = Ball_5 + 1 To 49

LOAD

$(Ball_1) as Ball1,

$(Ball_2) as Ball2,

$(Ball_3) as Ball3,

$(Ball_4) as Ball4,

$(Ball_5) as Ball5,

$(Ball_6) as Ball6

AutoGenerate 1;

Next Ball_6

Next Ball_5

Next Ball_4

Next Ball_3

Next Ball_2

Next Ball_1

though these loops will take a lot of time.

The way Karl suggested will be probably a lot faster, but the current script snippet will create a lot of records with duplicate Ball values like 1-1-1-1-1-1 that are not wanted and not possible in a draw.

I think you can change it to something like below to create the same results like your for-loops (i.e. do a subsequent load to filter the records you don't want):

TMP:

LOAD recno() as Ball1 AutoGenerate 44;

join LOAD recno() as Ball2 AutoGenerate 45;

join LOAD recno()as Ball3 AutoGenerate 46;

join LOAD recno() as Ball4 AutoGenerate 47;

join LOAD recno() as Ball5 AutoGenerate 48;

join LOAD recno() as Ball6 AutoGenerate 49;

Store TMP into Lottery.qvd (qvd);

drop table TMP;

RESULT:

LOAD * from Lottery.qvd (qvd) where Ball1<Ball2 and Ball2<Ball3 and Ball3<Ball4 and Ball4<Ball5 and Ball5<Ball6;

You need to have enough resources to produce your tables 😉

Regards,

Stefan