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