Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have four tables of data in my script. Each table has all identical fields.
The tables/fields are:
[Table 2012-13]:
LOAD UPN,
Year,
Points,
'' as Points_Difference
FROM...
[Table 2013-14]:
LOAD UPN,
Year,
Points,
Points - Lookup('Points','UPN',UPN, 'Table 2012-13') as Points_Difference
FROM...
[Table 2014-15]:
LOAD UPN,
Year,
Points,
Points - Lookup('Points','UPN',UPN, 'Table 2013-14') as Points_Difference
FROM...
[Table 2015-16]:
LOAD UPN,
Year,
Points,
Points - Lookup('Points','UPN',UPN, 'Table 2014-15') as Points_Difference
FROM...
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:
Year | Points | Points_Difference |
---|---|---|
2012/13 | 27 | |
2013/14 | 30 | 3 |
2014/15 | 32 | 3 |
2015/16 | 35 | 3 |
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.
Is anyone able to see where I've gone wrong?
Many thanks,
Jess
Hi Jessica,
Here's a simple solution.
I let you replace LOAD INLINE by your LOAD FROM ...
//[Table 2012-13]:
T_Data:
LOAD * INLINE [
UPN, Year, Points
1, 2012, 10
2, 2012, 11
3, 2012, 12
4, 2012, 13
];
//[Table 2013-14]:
LOAD * INLINE [
UPN, Year, Points
1, 2013, 11
2, 2013, 12
3, 2013, 13
4, 2013, 14
];
//[Table 2014-15]:
LOAD * INLINE [
UPN, Year, Points
1, 2014, 13
2, 2014, 14
3, 2014, 15
4, 2014, 16
];
//[Table 2015-16]:
LOAD * INLINE [
UPN, Year, Points
1, 2015, 16
2, 2015, 17
3, 2015, 18
4, 2015, 19
];
//Associate previous Point to current Points
left join (T_Data)
load UPN,
Year + 1 as Year,
Points as PreviousPoints
resident T_Data;
//Do calculation of Points difference
left join (T_Data)
load UPN,
Year,
Points - PreviousPoints as Points_Difference
resident T_Data;
//Remove previous Points value
DROP Field PreviousPoints;
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...
I think I might have some ideas... but being online only with my Samsung Note 4 at the moment I will have to get back to you later today. Maybe.some others pitch in before I have time...
Hi Jessica,
Here's a simple solution.
I let you replace LOAD INLINE by your LOAD FROM ...
//[Table 2012-13]:
T_Data:
LOAD * INLINE [
UPN, Year, Points
1, 2012, 10
2, 2012, 11
3, 2012, 12
4, 2012, 13
];
//[Table 2013-14]:
LOAD * INLINE [
UPN, Year, Points
1, 2013, 11
2, 2013, 12
3, 2013, 13
4, 2013, 14
];
//[Table 2014-15]:
LOAD * INLINE [
UPN, Year, Points
1, 2014, 13
2, 2014, 14
3, 2014, 15
4, 2014, 16
];
//[Table 2015-16]:
LOAD * INLINE [
UPN, Year, Points
1, 2015, 16
2, 2015, 17
3, 2015, 18
4, 2015, 19
];
//Associate previous Point to current Points
left join (T_Data)
load UPN,
Year + 1 as Year,
Points as PreviousPoints
resident T_Data;
//Do calculation of Points difference
left join (T_Data)
load UPN,
Year,
Points - PreviousPoints as Points_Difference
resident T_Data;
//Remove previous Points value
DROP Field PreviousPoints;
Hi Sébastien,
Thanks so much, that seems to be working perfectly.
Very much appreciated!
Best wishes,
Jess