topic Re: Calculation on Inter Record during Load? in QlikView

Calculation on Inter Record during Load?

itatspectrum — Wed, 25 Nov 2020 16:16:04 GMT

Hi All.

I've the following set of data which I need to process. I need to calculate the improvement over time so working out the difference between the term dates.

Student	TermDate	Rating
Steve	01/09/2015	7
Steve	01/12/2015	9
Steve	01/04/2016	12
Josh	01/09/2015	5
Josh	01/12/2015	5
Josh	01/04/2016	10

My table should look something like.

Student	TermDate	Rating	Score
Steve	01/09/2015	7	NA or 0
Steve	01/12/2015	9	2
Steve	01/04/2016	12	3
Josh	01/09/2015	5	NA or 0
Josh	01/12/2015	5	0
Josh	01/04/2016	10	5

I'm sure there is something on Peek with first sorted value but not sure as to syntax or the script to get into this format.

Thanks

Steve

Re: Calculation on Inter Record during Load?

florentina_doga — Fri, 08 Jul 2016 12:35:36 GMT

try this script

aa:

load * inline [Student, TermDate, Rating

Steve, 01/09/2015, 7

Steve, 01/12/2015, 9

Steve, 01/04/2016, 12

Josh, 01/09/2015, 5

Josh, 01/12/2015, 5

Josh, 01/04/2016, 10];

NoConcatenate

bb:

load * resident aa order by Student, TermDate;

drop table aa;

NoConcatenate

cc:

load *,

if(Student=previous(Student),Rating-previous(Rating),' NA or 0') as score

resident bb;

drop table bb;

hope this help.

Re: Calculation on Inter Record during Load?

sunny_talwar — Fri, 08 Jul 2016 12:57:43 GMT

I think order by is important here... try this:

Table:

LOAD Student,

TermDate,

Rating

FROM

[https://community.qlik.com/thread/224311]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(Student = Previous(Student), Rating - Previous(Rating), 'NA or 0') as Score

Resident Table

Order By Student, TermDate;

DROP Table Table;