topic Re: Help with Random Selection in QlikView

Help with Random Selection

gerhardl — Tue, 11 Sep 2012 13:17:12 GMT

Hi there,

I please need help with selecting random accounts that match certain criteria.

Scenario:

Out of about a million accounts, we do Statement checks once a month to ensure the statements are correct for all the different scenarios. We also have 12 different Account Types (companies), and we will do one account per scenario for each of the companies.

There are about 40 different scenarios, for instance, we will select one account percompany that has a D Block Code. Or one account per company that opened in the last month. Currently we make the selections manually (they do this out of different excel files), and then randomly select accounts.

So what I would love is to add this to my QlikView module:

A table box or chart that randomly selects one account per scenario, per company (please see the attached image).

Any ideas? I know the RAND() function will be involved, but so far I haven't been able to figure it out.

Thanks in advance,

Gerhard

Re: Help with Random Selection

whiteline — Tue, 11 Sep 2012 13:55:39 GMT

Then I guess, you need FieldValue, NoOfFieldsRows, Ceil/Foor and maybe LookUpPeek functions.

Re: Help with Random Selection

gerhardl — Tue, 11 Sep 2012 13:59:56 GMT

Hi whiteline,

Can you maybe give some more detail - I have never used any of those functions. I'll read up on them in the mean time.

Here is one example of what I need:

Show one random [Account No] with [Company] as dimension, where the [Account Status]='Active'

Re: Help with Random Selection

whiteline — Tue, 11 Sep 2012 15:31:17 GMT

I'v made some reasearch.

The easiest way to do it is in load script.

Consider that you have an input table from where you want randomly select one row.

LET RandomRow = Ceil(Rand()*NoOfRows('InputTable'))+1;

LOAD

Peek('KeyFieldFromInputTable', $(RandomRow), 'InputTable') as KeyField;

Peek('Field1FromInputTable', $(RandomRow), 'InputTable') as RandomField1;

Peek('Field2FromInputTable', $(RandomRow), 'InputTable') as RandomField2;

...

AUTOGENERATE (1);

KeyField you can use to connect your random table with the rest of data model,

so you don't have to copy every field only that you'll use as dimension.

Re: Help with Random Selection

gerhardl — Wed, 12 Sep 2012 09:45:59 GMT

Thanks for the trouble in helping me.

This is very new to me so I'm trying to take it step by step - having some trouble with the load.

I added to the top:

LET RandomRow = Ceil(Rand()*NoOfRows('InputTable'))+1;

Then I load my Account_Extract table (I load multiple other tables as well, but they are not relevant to what I want to do here).

Please see my image and have a look at what I am doing wrong.

When I load I get the error:

Error in expression:

')' expected

Random_Table:

LOAD

Peek('Account no', , 'Account_Extract') as KeyField

AUTOGENERATE (1)

Re: Help with Random Selection

whiteline — Wed, 12 Sep 2012 10:10:49 GMT

First, you can't load random row before you load your data (the NoOfRows function returns null and therefore error).

Add all strings after loading Account_Extract table.

Second, correct the random expression:

LET RandomRow = roundfloor(Rand()*(NoOfRows(Account_Extract)));

and correct peek expression (there should be 2 fields acctually):

Peek('Account no', , 'Account_Extract') as KeyField [Account no],

Peek('Account no', , 'Account_Extract') as [Random account no]

otherwise it lose connection with Account_Extract table.

Then use [Random account no] just as dimension in your charts.

Re: Help with Random Selection

gerhardl — Wed, 12 Sep 2012 10:40:16 GMT

Okay, getting closer (although I still have no idea how the chart will work... but one step at a time).

LET RandomRow = round(Rand()*(NoOfRows(Account_Extract)));

Account_Extract:

LOAD Field1,

Field1,

[Account no] as [Account No], //I must change to Accont No -this is a key field used in later tables.

etc

FROM [....]

WHERE (...);

Random_Table:

LOAD

Peek('Account no'$(RandomRow) , 'Account_Extract') as [Account no],

Peek('Account no'$(RandomRow) , 'Account_Extract') as [Random account no]

AUTOGENERATE (1);

It then loads fine - but when I create a list box of [Random account no] it is empty.

Re: Help with Random Selection

whiteline — Wed, 12 Sep 2012 16:50:26 GMT

//I must change to Accont No -this is a key field used in later tables.

Why didn't you change it in peek also ?

Peek('Account no', $(RandomRow) , 'Account_Extract') as [Account No],

The purpose of that field is the same - key field.

You also missed the ',' in peek (look at help to get better understanding of what you're doing):

Peek('Account no', $(RandomRow) , 'Account_Extract') as [Account No],

Peek('Account no', $(RandomRow) , 'Account_Extract') as [Random account no]

As for chart. Use [Random account no] as a dimension and you will get only this randomly selected data.

I guess, its only an account name, so you can add another fields in this random table with the same approach (peek()).

Re: Help with Random Selection

gerhardl — Mon, 17 Sep 2012 09:51:37 GMT

I did have the comma there originally, but when loading I get the attached error message (Error in Expression.jpg). When I remove the comma it loads correctly (Script Execution.jpg). From RANDOM TABLE it says "1 lines fetched" - but when I create list boxes I still have no account in the Random Field. Also, under Account No (my key field), right at the bottom where it shows the number of records, it shows "0+12355", which I have never seen before (List Boxes.jpg).

Re: Help with Random Selection

whiteline — Mon, 17 Sep 2012 10:44:10 GMT

The error message says clearly that RandomRow has null value.

Add quotes:

LET RandomRow = floor(Rand()*(NoOfRows('Account_Extract')));

Re: Help with Random Selection

gerhardl — Mon, 17 Sep 2012 10:52:01 GMT

Not sure where this error message is that says that clearly - the only error message I get is the one that says "Error in Expression: ')' Expected" - that is fixed when I remove the comma, as I mentioned earlier.

I added the quotes to the LET statement and it made no difference - exact same result.

This gives me the error message during the load:

Random_Table:

LOAD

Peek('Account no', $(RandomRow) , 'Account_Extract') as [Account No],

Peek('Account no', $(RandomRow) , 'Account_Extract') as [Random account no]

AUTOGENERATE (1);

When I remove the comma after 'Account no' it loads with no problem.

Sorry - I am really trying to follow your instructions to the letter here.

Re: Help with Random Selection

gerhardl — Mon, 17 Sep 2012 11:00:51 GMT

Okay I think I found my mistake - will check if I can figure it out and reply a bit later.

Thank you for all the help so far.

Re: Help with Random Selection

gerhardl — Mon, 17 Sep 2012 11:29:57 GMT

Okay I've now managed to load one random account number using the load script.

Now my problem is that I have different scenarios, and for each of them I need different random account numbers, per company.

So let's say I have 10,000 accounts. I randomly want to select one account WHERE the Account Status is "Active", and I want to do that for each company. Then I randomly want to select one account for each company where the Account Status is "Dormant". All of these other fields, which I would use in the expression, is also on the Account_Extract table.

At the moment the Random Account gets chosen from the entire Account_Extract table - regardless of the Account Status or anything else.

Eventually I am actually going to need maybe 50 or 100 account numbers that are selected randomly AFTER the scenario's rules are applied.

So I don;t know if this can all be done using the load? Maybe if I have multiple RANDOM_tables (for each scenario), and then I do sometyhing like LOAD a random account FROM the Account_Extract table, WHERE field X is equal to Y, etc.

But not sure if that is possible.

If this is all too hard to accomplish I will ahve to figure out something else - I don;t want to waste any more of your time if we can't do this.

Re: Help with Random Selection

whiteline — Mon, 17 Sep 2012 14:13:18 GMT

You can handle all your tasks with the same approach:

"Use peek with random index to select random field value. Use shared key field to connect it to your data."

You don't have to use variable for each random selection (just substitute the expression).

Just prepeare the field with list of values according to your criteria and then use peek to get one random value.