I can't instantly think of a simple solution but here is a general suggestion:
As you're wanting 10 records per month within the data you're probably going to have to create a loop in the script to go through each month seperately.
1. Load the entire dataset and add a rand() function to each record, sort the table by Month then your new random column.
2. Generate a Temp table containg all the possible months to control your loop.
3. Loop through each Month and perform a Resident > concatenate load for each month limiting the load each time with a Where clause (recno() <=10 ?).
4. You should now have a table containing the data you need, you can drop the previous tables.
The above is unchecked so you may need to adjust slightly, hopefully it will provide you with a starting point.
All the best,
Matt - Visual Analytics Ltd
Something like this (using Sample though instead of Rand):
Floor(MonthStart(OrderDate)) As MonthKey,
SQL SELECT *
Distinct MonthKey as MonthList
Resident Orders_Temp Order by MonthKey;
For i = 0 to FieldValueCount('MonthKey')-1
vMonth = Peek('MonthList', $(i), 'Month_Temp');
TRACE $(i) $(vMonth);
Where MonthKey = $(vMonth)
AND RowNo() < 10
Load * Resident Orders_Temp2;
Drop Table Orders_Temp2;
Drop Table Orders_Temp;
Check out the sample statement from the QV reference manual. You'll probably need to use variables or something to get the first 100 or you might be able to use the FIRST statement if it spits the table out randomly but off the top of my head I don't know if it does.
The sample prefix is used to load a random sample of records from the input
The syntax is:
sample p ( loadstatement | selectstatement )
p is an arbitrary expression which evaluates to a number larger than
0 and lower or equal to 1. The number indicates the probability for a
given record to be read.
Sample 0.15 Select * from Longtable;
Sample 0.15 Load * from Longtab.csv;
The load statement syntax is described on page 309. The select statement
syntax is described on page 329.