Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Looping Data in the Script (Snapshot)

I have a table like this:

  

StudentIdStudentNameYearIn
S001Sandra2009
S002Denny2010
S003Andri2011
S004Wilson2012

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

   

YearSnapshotStudentIdStudentNameYearIn
2009S001Sandra2009
2010S001Sandra2009
2010S002Denny2010
2011S001Sandra2009
2011S002Denny2010
2011S003Andri2011
2012S001Sandra2009
2012S002Denny2010
2012S003Andri2011
2012S004Wilson2012

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

Thank you

1 Reply
Highlighted

Re: Looping Data in the Script (Snapshot)

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;


Capture.PNG