generate date for intervalMatch

[thierrytt1102]

Dear,

I've need to create an historic based on a interval match I want to generate. For my interval match, I need 2 fields. DATE FROM, DATE TO. But I don't have the Date_To as this is the DATE_From of the next line.

EG.

Name      Team          EnterDate

Barack          A           01/01/2014

Barack          B           10/01/2014

Barack          C           20/01/2014

Bill                A           02/01/2014

I'd like to have this:

Name          Team     EnterDate     OutDate

Barack           A         01/01/2014     10/01/2014

Barack           B         10/01/2014      20/01/2014

Barck             C          20/01/2014     Present

Bill                 A         02/01/2014      Present

How could I generate the forth column?

Thx for your help

TT

[anbu1984]

Initial:

Load *,RowNo() As Rn;

Load * Inline [

Name,Team,EnterDate

Barack,A,01/01/2014

Barack,B,10/01/2014

Barack,C,20/01/2014

Bill,A,02/01/2014 ];

Final:

NoConcatenate

Load Name,Team,EnterDate, If(IsNull(Previous(EnterDate)) Or Date#(Previous(EnterDate),'d/m/yyyy') < Date#(EnterDate,'d/m/yyyy') ,Date#(Today(),'d/m/yyyy'),Previous(EnterDate)) As OutDate Resident Initial order by Rn desc;

Drop table Initial;

[thierrytt1102]

HI,

Thx for your response. It's almost the result but it doesn't work if Barack come back on his initial team.

Initial:

Load *,RowNo() As Rn;

Load * Inline [

Name,Team,EnterDate

Barack,A,01/01/2014

Barack,B,10/01/2014

Barack,C,20/01/2014

Bill,A,02/01/2014

Barack, A, 03/02/2014 ];

Don't we have to make something like  previous(Name&EnterDate) ?

Thank you very much

[anbu1984 · Accepted solution]

Initial:

Load Name,Team,Date#(EnterDate,'D/M/YYYY') As EnterDate;

Load * Inline [

Name,Team,EnterDate

Barack,A,01/01/2014

Barack,B,10/01/2014

Barack,C,20/01/2014

Bill,A,02/01/2014

Barack,A,03/02/2014 ];

Final:

NoConcatenate

Load Name,Team,EnterDate, If(IsNull(Previous(EnterDate)) Or Name <> Previous(Name),Date#(Today(),'D/M/YYYY'),

Previous(EnterDate)) As OutDate Resident Initial order by Name,EnterDate desc;

Drop table Initial;

[hic]

See http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/25/creating-intervals-from-change-dates

HIC

[MarcoWedel]

Hi TT,

one solution could be:

tabInput:
LOAD * INLINE [
    Name, Team, EnterDate
    Barack, A, 01/01/2014
    Barack, B, 10/01/2014
    Barack, C, 20/01/2014
    Bill, A, 02/01/2014
];

tabOutput:
LOAD
  Name,
  Team,
  EnterDate,
  If(Name=Peek(Name),Peek(EnterDate), Dual('Present',Today())) as OutDate;
LOAD * Resident tabInput Order By Name, EnterDate desc;

DROP Table tabInput;

hope this helps

regards

Marco