Looping Data in the Script (Snapshot)

Tue, 05 Jan 2016 09:56:33 GMT

I have a table like this:

I want to make a snapshot table based on the table above, so the result should be like this:

Can you help me the best practice to get the result?

Thank you

Re: Looping Data in the Script (Snapshot)

sunny_talwar — Tue, 05 Jan 2016 14:57:46 GMT

This may be:

Table:

LOAD * Inline [

StudentId, StudentName, YearIn

S001, Sandra, 2009

S002, Denny, 2010

S003, Andri, 2011

S004, Wilson, 2012

];

Join(Table)

LOAD Max(YearIn) as MaxYear,

Min(YearIn) as MinYear

Resident Table;

FinalTable:

LOAD StudentId,

StudentName,

YearIn,

YearSnapshot

Where YearSnapshot <= MaxYear;

LOAD StudentId,

StudentName,

YearIn,

MaxYear,

YearIn + (If(YearIn > MinYear, YearIn, MinYear) - YearIn + IterNo()) - 1 as YearSnapshot

Resident Table

While (MinYear + IterNo() - 1) <= MaxYear;

DROP Table Table;