Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
thierrytt
Contributor

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
Honored Contributor III

Re: generate date for intervalMatch

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;

5 Replies
anbu1984
Honored Contributor III

Re: generate date for intervalMatch

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
Contributor

Re: generate date for intervalMatch

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
Honored Contributor III

Re: generate date for intervalMatch

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;

Employee
Employee

Re: generate date for intervalMatch

Re: generate date for intervalMatch

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

Community Browser