Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
subbareddykm
Creator II
Creator II

peek Logic

Hi all,

i need one logic. i.e

I have project, phase and date like below

   

ProjectNamePhaseQAFPartsReceiptPlanDate
55606/8/2016
5561166/8/2016
5561166/10/2016
5561166/14/2016
5561166/15/2016
5561166/30/2016
5561167/8/2016
5561167/14/2016
5561167/15/2016

For every project , every phase pick min and Max date and  Like below

      

ProjectNamePhaseQAFPartsReceiptPlanDateOutputLogic
55606/8/20166/8/2016min date project and Phase 0
5561166/8/20166/8/2016min date project and Phase 116
5561166/10/20166/15/2016above date + 7
5561166/14/20166/22/2016above date + 7
5561166/15/20166/29/2016above date + 7
5561166/30/20167/6/2016above date + 7
5561167/8/20167/13/2016above date + 7
5561167/14/20167/20/2016above date + 7
5561167/15/20167/27/2016above date + 7

Can any body tell me how to get this.

Note: +7 should not exceed Max date of phase and If it exceeds Pick max date instead.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Piggy backing on Massimo's code to get the upper range limit:

t:

load * inline [

ProjectName, Phase, Date

556, 0, 6/8/2016

556, 116, 6/8/2016

556, 116, 6/10/2016

556, 116, 6/14/2016

556, 116, 6/15/2016

556, 116, 6/30/2016

556, 116, 7/8/2016

556, 116, 7/14/2016

556, 116, 7/15/2016

];

Join(t)

LOAD ProjectName,

  Max(Date) as ProjectMaxDate

Resident t

Group By ProjectName;

 

t2:

LOAD *,

  If(ProjectName <> Peek('ProjectName') Or Phase <> Peek('Phase'),

  Date,

  Date(RangeMin(Date(Peek('Output') + 7), ProjectMaxDate))

  ) as Output

Resident t

order by ProjectName, Phase, Date;

DROP Table t;

t1.PNG

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
maxgro
MVP
MVP

t:

load * inline [

ProjectName, Phase, Date

556, 0, 6/8/2016

556, 116, 6/8/2016

556, 116, 6/10/2016

556, 116, 6/14/2016

556, 116, 6/15/2016

556, 116, 6/30/2016

556, 116, 7/8/2016

556, 116, 7/14/2016

556, 116, 7/15/2016

];

t2:

load *,

  if(ProjectName <> Peek('ProjectName') or Phase <> Peek('Phase'), Date, Date(Peek('Output') +7)) as Output

Resident t

order by ProjectName, Phase, Date;

DROP Table t;

1.png

subbareddykm
Creator II
Creator II
Author

Exactly same but it should not exceed my max date Check above

Example :

project=556,pHase=116 my max date is 7/15/2016 but output date 7/27/2016


If Date is exceeding output date Pick max date instead


I want to apply +7 for In between Min and Max date of of my Phase





jonathandienst
Partner - Champion III
Partner - Champion III

Piggy backing on Massimo's code to get the upper range limit:

t:

load * inline [

ProjectName, Phase, Date

556, 0, 6/8/2016

556, 116, 6/8/2016

556, 116, 6/10/2016

556, 116, 6/14/2016

556, 116, 6/15/2016

556, 116, 6/30/2016

556, 116, 7/8/2016

556, 116, 7/14/2016

556, 116, 7/15/2016

];

Join(t)

LOAD ProjectName,

  Max(Date) as ProjectMaxDate

Resident t

Group By ProjectName;

 

t2:

LOAD *,

  If(ProjectName <> Peek('ProjectName') Or Phase <> Peek('Phase'),

  Date,

  Date(RangeMin(Date(Peek('Output') + 7), ProjectMaxDate))

  ) as Output

Resident t

order by ProjectName, Phase, Date;

DROP Table t;

t1.PNG

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
subbareddykm
Creator II
Creator II
Author

I have one doubt here if it exceed my max i dont want to pick max date and i want to stop there itself ,then where i need to change  ?

Exp :

In output  Phase=116 ,07/15/2016  that we are picking from actual date right . i want to stop 07/13/2013 .

Can tell me where i need to change from script ?