topic Re: Issue with calculated field in QlikView

Issue with calculated field

jessica_webb — Fri, 08 Jan 2016 13:57:35 GMT

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

Re: Issue with calculated field

petter — Fri, 08 Jan 2016 14:18:43 GMT

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.

Re: Issue with calculated field

jessica_webb — Fri, 08 Jan 2016 14:25:22 GMT

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

Re: Issue with calculated field

petter — Fri, 08 Jan 2016 14:29:34 GMT

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

Re: Issue with calculated field

sfatoux72 — Fri, 08 Jan 2016 15:12:37 GMT

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;

Re: Issue with calculated field

jessica_webb — Fri, 08 Jan 2016 15:32:44 GMT

Hi Sébastien,

Thanks so much, that seems to be working perfectly.

Very much appreciated!

Best wishes,

Jess