Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gerhardl
Contributor II

Help with Random Selection

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

Tags (1)
13 Replies
whiteline
Honored Contributor II

Re: Help with Random Selection

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

gerhardl
Contributor II

Re: Help with Random Selection

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
Honored Contributor II

Re: Help with Random Selection

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
Contributor II

Re: Help with Random Selection

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
Honored Contributor II

Re: Help with Random Selection

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
Contributor II

Re: Help with Random Selection

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
Honored Contributor II

Re: Help with Random Selection

//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
Contributor II

Re: Help with Random Selection

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
Honored Contributor II

Re: Help with Random Selection

The error message says clearly that RandomRow has null value.

Add quotes:

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

Community Browser