Macro to do random selections based on certain criteria

[gerhardl]

Hi,

I please need help cleaning up or totally changing a macro I have been using. This will be a long post - I apologize in advance.

I'll try and put current functionality in a nutshell:

The purpose is to provide a user with an Excel file with RANDOM account numbers that meet certain criteria. There are about 50 different criteria types (e.g. Account older than 6 months with a debit balance and 2 specific insurance products selected and their chosen language as Zulu), and then each criteria type must show one RANDOMLY selected account number PER product (of which there are about 20).

I load almost a million records from text files into a QV table. it contains full account detail.

I then created a bookmark for each criteria type and product combination (back then there were fewer criteria and products but I still ended up with about 190 bookmarks. took me for ever).

Then I created a macro which selects one bookmark and sends all possible account numbers from a table box to an excel file (column A). There can be 20 accounts or 250,000 accounts meeting the bookmark selections. In cell B1 of the Excel file the macro then adds a formula which selects one random value from column A. The macro then keeps this one random value and deletes the entire Account list from Column A.

And then I repeat the macro code for each and every bookmark I have, but each time I change the bookmark name and the location of the excel formula, so eventually I end up with my random selections. Right at the end the macro adds the bookmark descriptions to column A and the Product Names as headers. See the Excel Sample image attached.

The macro looks like this, but it is repeated for each bookmark (the red parts are changed for each repetition):

'<<<<<<New Account Product 5 (start)>>>>>>>>>

ActiveDocument.RecallDocBookmark "New5" 'Select Bookmark

XLApp.Worksheets(1).Range("A1:A500000").NumberFormat = "@"

set LB = ActiveDocument.GetSheetObject("LB28")

boxvalues=LB.GetPossibleValues

for i = lbound(boxvalues) to ubound(boxvalues)

XLSheet1.cells(i+1,1).Value = boxvalues(i)

next

XLSheet1.Range("B3").Formula = "=INDIRECT(""A"" & INT(RAND()*COUNTA(A:A))+1)"

XLApp.Worksheets(1).Range("B4") = XLApp.Worksheets(1).Range("B3")

XLApp.Worksheets(1).Range("B3") = XLApp.Worksheets(1).Range("B4")

XLApp.Worksheets(1).Range("B4").Delete

XLApp.Worksheets(1).Range("A:A").Delete

XLApp.Worksheets(1).Range("A:A").Insert

'<<<<<<New Account Product 5 (end)>>>>>>>>>

Currently this macro runs an hour or more. It is almost 4,000 lines. Plus the code is a mess, and each time the users want to add or change criteria, or when new products are added it takes me hours to update this.

I need to clean this up somehow.

I'd like to change my macro to do the following, but have no idea how:

Select each bookmark that exists and follow my above code, and then just loop through each and every bookmark and repeat the same step, but each time the values in red also would need to change. Plus I need to cater for the different products, which act almost like a dimension split.

Best case scenario is to have a straight table in QlikView with Product as dimension and each Criteria Type as an expression, giving me one random account number - but this doesn't seem possible. The QlikView rand() function doesn't seem able to meet my requirements.

I hope the attached image of the end result helps to make this clear, and I hope there is a better way of doing this.

Thanks,

Gerhard

[Gysbert_Wassenaar]

Create an extra field in the table with the account details to give every account a random number.

ceil(rand() * 10000000) as random_value.

Every time you reload the accounts will get a new random random_value value.

You can use this with the rank function and/or dimension limits to limit the results to for example 50 values. The resulting set should then have 50 random account numbers.

[gerhardl]

Hi,

Unless I misunderstand this would not solve the problem. I do not need 50 random account numbers.

I need one random account number for each bookmark or set of selection criteria.

So if one criteria is:

account in Active status with a debit balance and FPP insurance where the customer is English speaking and the account was opened in the last cycle.

Then I need only one random such account.

Then there are 50+ such criteria types.

This cannot be done in the load script I think