Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
thierrytt1102
Partner - Creator II
Partner - Creator II

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;

thierrytt1102
Partner - Creator II
Partner - Creator II
Author

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;

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