Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;