Split records in to batches

[gerhardl]

Hi,

I have a module which I use with macros to auto-generate batches of excel files, which are then used to send SMSes to customers that meet certain criteria. My data source is our full customer database, and I then try to split it into batches of 25,000 and then I have a macro which generates an excel file for each batch.

Currently I have very "ugly" logic - I use the row number of the loaded table and if it's between a specific range I decide which batch number it belongs to. This is from my load script:

if((rowno()<=45000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 1',
if((rowno()>45000 and rowno()<=70000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 2',
if((rowno()>70000 and rowno()<=95000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 3',
if((rowno()>95000 and rowno()<=120000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 4',
if((rowno()>120000 and rowno()<=145000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 5',
if((rowno()>145000 and rowno()<=170000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 6',
if((rowno()>170000 and rowno()<=195000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 7',
if((rowno()>195000 and rowno()<=220000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 8',
if((rowno()>220000 and rowno()<=245000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 9',
if((rowno()>245000 and rowno()<=270000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 10',
if((rowno()>270000 and rowno()<=295000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 11',
if((rowno()>295000 and rowno()<=320000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 12',
if((rowno()>320000 and rowno()<=345000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 13',
if((rowno()>345000 and rowno()<=370000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 14',
if((rowno()>370000 and rowno()<=395000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 15',
if((rowno()>395000 and rowno()<=420000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 16',
if((rowno()>420000 and LEFT([Cell phone], 7) <> '+264(0)'),'CD1 SA Batch 17',
if(LEFT([Cell phone], 7) = '+264(0)',null(),'Error')))))))))))))))))) as SA_Reminder,

I just feel this is a very nasty way to go about it but I can't think of any alternatives - I also now want to change the batches to 15,000, which will make this even worse.

"SA" stands for South Africa, and I have a separate field for Namibia, so I decide the country of the customer based on the cell phone number and then create batches per country.

I feel there should be a way where I can have the batch size as a variable, and then QlikView automatically assigns batches, started at Batch 1 until all the records run out - each batch should have 15,000 (or whatever the variable is) records exactly, except the final batch which will have fewer records.

I also don't mind doing the batch splitting in the macro itself - currently I select each of the "SA_Reminder" field values and create a batch and then repeat until all field values have been selected.

[jonathandienst]

You could load the batch ranges and names into an inline or Excel table and then use intervalmatch to replace the nested ifs. This will also be relatively easy to change to smaller intervals as well.

I would parse the country from the cell phone number as a separate step, and then combine the results to create the final field value -- if the ranges are the same for all countries. If the ranges are different, then you would parse the country first and use the extended syntax for interval matching to perform country specific mappings.

If you post some sample data I could provide more specific help if you like.