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