Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
thierrytt
Partner
Partner

generate date for intervalMatch

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

1 Solution

Accepted Solutions
anbu1984
Master III
Master III

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;

View solution in original post

5 Replies
anbu1984
Master III
Master III

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;

thierrytt
Partner
Partner

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
Master III
Master III

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;

View solution in original post

MarcoWedel

Hi TT,

one solution could be:

QlikCommunity_Thread_117374_Pic1.JPG.jpg

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