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

[bharatkishore]

Done that..

[Kalmer]

Hey,
So old logic still continues, a new Condition needs to be added.
PS: as you can see i changed the Previous(Flag) = 0. I made the condition vice-versa.

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

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

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

[bharatkishore]

Sorry now for both flags i am getting continuous numbers.. even the earlier condition is not working...

[Kalmer]

My mistake.

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

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

That should do the trick

[bharatkishore]

Let me try to explain more clearly:

please see the below image

The install flag what you have given is perfectly working fine.

Now one more condition is that if you see in the above image i have removal flag so there i need to get accumulated value till where there is 1. And from next all records  should become 0. There shouldn't be any more numbers.

Please let me know if something is not clear.

Thanks,

Bharat

[Kalmer]

Check my previous post, that should do it

[bharatkishore]

I tried with that one.Please see the below image:

Here it 222 it is fine but from next i should get all 0 no more 1,2,3,.. and this should happen only for removal flag and not for install flag. Install flag is working perfectly fine..

[Kalmer]

so let me get this straight:
IF the Key_EMS changes, THEN we start from Month 1.
IF the Key_EMS is the same and the Install_Flag changes, THEN we start from Month 1,
IF the Key_EMS is the same and the Removal Flag comes in, THEN all values from next row in the same KEY_EMS = 0 (since it was removed) ?

I would do a preceeding load in here:

LOAD

*,

if(Previous(Removal_Flag) = 1, 0, if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1))) as Age2

;

LOAD

*,

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
;

[bharatkishore]

HI K,

Thanks a lot..

I have given the below expression in one table it-self:

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

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

     if(Previous(Removal_Flag) = 1, 0, if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1))))) as Age2

And it is perfectly working fine. Is this correct right condition right or anything wrong..

Thank you so much...

[Kalmer]

i did not undestand this part "Is this correct right condition right or anything wrong.."
But as i understood it solved the problem.
Have good one!