Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
itatspectrum
Partner - Contributor II
Partner - Contributor II

Calculation on Inter Record during Load?

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.

StudentTermDateRating
Steve01/09/20157
Steve01/12/20159
Steve01/04/201612
Josh01/09/20155
Josh01/12/20155
Josh01/04/201610

My table should look something like.

StudentTermDateRatingScore
Steve01/09/20157NA or 0
Steve01/12/201592
Steve01/04/2016123
Josh01/09/20155NA or 0
Josh01/12/201550
Josh01/04/2016105

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

2 Replies
florentina_doga
Partner - Creator III
Partner - Creator III

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;


Capture.PNG