Calculation on Inter Record during Load?

[itatspectrum]

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

[florentina_doga]

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.

[sunny_talwar]

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;