PDA

View Full Version : Random Sampler



rangudu_2008
09-20-2009, 06:48 AM
Dear All,

I'm looking to automate a manual process of sampling of records done as part of a checking process done on users processing out records of an automated monitoring system which auto-generates records based on a set of pre-defined rules. The sampling of records is being carried out every fortnight, and is being done manually for 2 different types of extracts (obtained from a very very big Oracle database table) - one with a few thousands of records (alerts) and another with few hundreds of records (cases).

The checking process (called QA henceforth) is based on the certain conditions.

The QA Framework is based on the risk weight and profiling of entities monitored by the system. Entities are tagged on a priority basis as L1, L2 and L3 called risk rating. The priority for processing the same done by users is L3 > L2 > L1.

I've developed a few macro modules to carry out the random sampling (as per the pre-defined process) -- but need some help to develop the module which takes care of the above conditions and then to integrate all the modules together & test it as a whole and remove bugs if any.

I'm herewith attaching the modules that have been developed so far, and the description of each are as below:
1. Rule Splitter -- Splits and extract the codes of the rules from the Notes column (listed on a separate sheet) [Needs to be tweaked a bit and integrated in main module]
2. Allocate -- Splits the entire extract into 3 different sheets based on L1, L2, L3.
3. Filter & Transfer -- Transfers the entire set of records into a new sheet where filters have been applied; filters can be upto any level.
4. Random Sampler -- Samples out x no. of records (based on Alert ID) input by the user [might need tweaking a bit]
5. Pivot Summary -- Summarizes the whole sheet into a pivot table as required.

The module that i need to develop and merge it with the existing random sampler module also needs tweaking -- My requirement's as below:

I'll have a list of names in a sheet (like in the Rule Splitter module where it has rules and risk weight) -- The end user will input the number of samples required against each name (those for whom samples will be chosen) and then click a button and for those users samples have to be chosen using the random sampler module.

I need a small tweaking prior to this step -- that is the new users who may be under training might sometimes process alerts of another existing user; such new users might need to be sampled out too. A separate track of all alerts processed by such new users exists in a separate workbook and that must be vlookuped and the names should be changed to new users from that of existing user.

Can anyone help me develop this module first and later to integrate all the modules together?

P.S: I'm attaching the modules, extract and sampling conditions in parts.

Ranga

Modules Part II attached.

Sample Extracts attached.

Conditions for Sampling.

Hi Aaron,

I'd be very glad if you could help me out on this.

Pls ignore all other posts / attachments in this thread.

Ranga