Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

Issue with calculated field

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 PointsPoints_Difference
2012/1327 
2013/14303
2014/15323
2015/16353

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

1 Solution

Accepted Solutions
sfatoux72
Partner - Specialist
Partner - Specialist

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;

View solution in original post

5 Replies
petter
Partner - Champion III
Partner - Champion III

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.

jessica_webb
Creator III
Creator III
Author

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...

petter
Partner - Champion III
Partner - Champion III

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...

sfatoux72
Partner - Specialist
Partner - Specialist

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;

jessica_webb
Creator III
Creator III
Author

Hi Sébastien,

Thanks so much, that seems to be working perfectly.

Very much appreciated!

Best wishes,

Jess