Help with Random Selection

[gerhardl]

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

[whiteline]

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

[gerhardl]

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'

[whiteline]

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.

[gerhardl]

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)

[whiteline]

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.

[gerhardl]

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.

[whiteline]

//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()).

[gerhardl]

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).

[whiteline]

The error message says clearly that RandomRow has null value.

Add quotes:

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