topic Re: generate date for intervalMatch in QlikView

generate date for intervalMatch

thierrytt1102 — Tue, 06 May 2014 11:51:17 GMT

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

Re: generate date for intervalMatch

anbu1984 — Tue, 06 May 2014 12:10:02 GMT

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;

Re: generate date for intervalMatch

thierrytt1102 — Tue, 06 May 2014 12:32:12 GMT

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

Re: generate date for intervalMatch

anbu1984 — Tue, 06 May 2014 12:53:09 GMT

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;

Re: generate date for intervalMatch

hic — Tue, 06 May 2014 18:31:59 GMT

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

HIC

Re: generate date for intervalMatch

MarcoWedel — Tue, 06 May 2014 22:11:54 GMT

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