I have four tables of data in my script. Each table has all identical fields.
The tables/fields are:
'' as Points_Difference
Points - Lookup('Points','UPN',UPN, 'Table 2012-13') as Points_Difference
Points - Lookup('Points','UPN',UPN, 'Table 2013-14') as Points_Difference
Points - Lookup('Points','UPN',UPN, 'Table 2014-15') as Points_Difference
In each table, I am trying to create a calculated field that gives me the difference between points for each UPN, each year.
These are the bits of script in red.
The reload etc works fine, however, when I create a table to show me the points table for each 'Year', I get the following:
It seems the lookup works in [Table 2012-13], as I specified '' as the value there. It's also working in [Table 2013-13] (difference between 30 and 27 shows as 3). But then it seems to stick at that value, and the calculation doesn't work for the remaining two tables.
Since these four tables are getting auto concatenated the tables are not four but just one in spite of the load script's table.labels.... So doing lookup to tables that are never materialised will fail.
Thanks Petter. Do you have any ideas how I could get round this?
I tried using a 'Noconcatenate load', but that just destroyed my reload - presuming it overwhelmed the system with synthetic keys. Same happened when I tried to rename the calculated fields to (e.g.) Points_Difference_13, Points_Difference_14...