Peek or Previous

[bharatkishore]

Hi All,

I have the below script:

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

NoConcatenate

T:

Load

      Equipment,

      System_Code,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS,

      Install_Flag ,

      Removal_Flag ,

Technical_Start_Date,

($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))) AS Age

Resident Equipments_Details_final;

DROP Table Equipments_Details_final;

Now in the front end i load all the fields in a chart as below image:

Now since the data is huge i just loaded one key.. Now for this particular key we have an install flag.. So whenever we have install flag as 1 the next row in the age should be starting from 1 and then goes on..

For example in the attached qvw we have install flag for Calendar_Month_Code 2013-12 and Technical_Start_Date 2000-09, So till there we can see age as 160 and now from next row i.e. 2014-01 the age should starts from 1.

Can you please tell me how can i do it.

Attached qvw for reference

Thanks,

Bharat

[lironbaram]

hi

try something like this

T:

Load

      Equipment,

      System_Code,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS,

      Install_Flag ,

      Removal_Flag ,

Technical_Start_Date,

if(previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

     if(Install_Flag =1,1,rangesum(peek('Age'),1))) AS Age

Resident Equipments_Details_final

order by Key_EMS,Calendar_Month_Code;

[bharatkishore]

Hi Liron,

Thanks a lot for your reply.

Only thing is as of now from where i have install flag there it is showing as 1 but 1 should come from the next record.

Attached image for more reference:

Here in the image we have install flag at 2016-11  and 1998-06 in technical start date. Here the age is starting from 1 , but instead of this till where i have install flag as 1 till there it should me 222 and the next record i.e. 2016-12  and technical start date 1998-06 from there it should start from 1.

Please let me know if you need anything more.

Thanks,

Bharat

[bharatkishore]

stalwar1‌ Sunny Bhai.. Can you please help..

[Kalmer]

Try this:

Piece of old code:

if(Install_Flag =1,1,rangesum(peek('Age'),1))) AS Age

Replace it with:

if(Previous(Install_Flag) <> InstallFlag, 1, Rangesum(Peek('Age'),1))) as Age

Good luck!

[bharatkishore]

Thank you Kalmer.

But the problem is please see the below image:

Here where install flag is there till there  i need to get the aggregated one. So here in this case i am getting 1 but there is should get 222 then from next record i should get 1 which now i am getting.

Just if we can get that 222 value there then it will done.

Thanks,

Bharat

[Kalmer · Accepted solution]

Strange, the last piece of script should have fixed it.

if(Previous(Install_Flag) <> InstallFlag, 1, Rangesum(Peek('Age'),1))) as Age  - As in, if last row was different from current row we start the Age.
Hmm try then (same row):
if(Previous(InstallFlag) = 1,1,rangesum(peek('Age'),1))) AS Age

NP!
K

[bharatkishore]

Wow.. this worked.. Thank you so much K.

But can you please tell me what will happen with the logic you have written.

[Kalmer]

If you mean in this Thread (Re: Need to calculate Age), then it operates pretty much the same way.
But i guess we miss-spoke with the Install/Removal Flags.

Could you please put a link TO THIS POST in the post mentioned above in THIS comment. Also putting a "Correct" wouldn't harm me

[bharatkishore]

Can i ask you one question please,

Now in this code i need to pass one more else condtion

if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

     if(Previous(Install_Flag) = 1,1,rangesum(peek('Age'),1))) AS Age

like where removal flag is 1 till there i need to get aggregated value and from the next row it should be 0 for all the remaining rows.

Can you please help me how to do it.

Please don't mind.

Thanks,

Bharat