Hmmm, it's taken a while to unpick your script a bit and I think you need to look carefully at your data model. At this stage I'll try some generic advice as it's difficult to do more without an example of the source data...
1. (not really related to the question) Consider using ApplyMap() with source or inline mapping tables instead of your IF() statements. Much more expandable.
2. Look at using a composite key to join the tables, something like PROGRAM_CODE & Residency & 'New/Continuing' AS %KEY. Obviously you will need to substitute Residency and 'New/Continuing' for the logic that defines these fields. Essentially, you need to build a key that joins all the bits of data you want to compare between the two tables. You will then need to remove PROGRAM_CODE from one of the tables - ensure you leave it in the one that is guaranteed to have all possible values.
Hope this helps a bit - if you post some source data we can help further.
Thanks for getting back to me and thanks for the advice on ApplyMap I will have a bash at that this afternoon.
I have attached the source data and the latest version of the QVW file I have been working on. This is not the actual data as we will use as we are still building the data model and waiting for the server to be installed. So I put together this test data to enable me to start practicing before we get started. However these are some of the issues we will encounter.
Table one is the Conversion_Reg_Data file
Table 2 is the 20120221PlannedNumbersTemplateSIDV1 file this table holds the target numbers.
The biggest problem for me is that I have no control on the format of the the target numbers in the planning template. I had thought about using a composite key, but I can’t work out how I will join these fields as the key is dependent upon the selection of conditions - Region Home/EU or Overseas and New or ALL with the main data!