Looping Data in the Script (Snapshot)

[#1]

I have a table like this:

StudentId	StudentName	YearIn
S001	Sandra	2009
S002	Denny	2010
S003	Andri	2011
S004	Wilson	2012

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

YearSnapshot	StudentId	StudentName	YearIn
2009	S001	Sandra	2009
2010	S001	Sandra	2009
2010	S002	Denny	2010
2011	S001	Sandra	2009
2011	S002	Denny	2010
2011	S003	Andri	2011
2012	S001	Sandra	2009
2012	S002	Denny	2010
2012	S003	Andri	2011
2012	S004	Wilson	2012

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

Thank you

[sunny_talwar]

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;