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]

Hi K,

Just a question,

Need to pass one more else condition in the below formula is

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

Now where ever i have install and removal flag as 1 then next row should be all 0.

For example please see the below image:

Here till 75 is fine but from next row it should be all 0.

Can you please help.

Thanks,

Bharat

[Kalmer]

Hi!

You just need to create all the results for all combinations possible. (It should be pretty easy from here, since you have most of the script done).
Try this:

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

if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 1, 0,

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

if(Previous(Install_Flag) = 0 and Previous(Removal_Flag) = 1, 0,

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

K

[bharatkishore]

Thanks a lot K. You are simplly amazing.. Thank you so much..

[bharatkishore]

Hi K,

One final question on this topic hopefully....

Please see the below image

Here if you see in KEY_EMS there we have some nulls and if you see in the Age2 column there the subtraction is not happening correct(between technical_start_Date - Calendar_Month_Code) and the months are showing in-correct.

Can you please check and let me know what i need to do.

Thanks,

Bharat

[Kalmer]

1) Make sure your order is in asc in script:

YourTable:
Load
*

FROM (Connection)

Where clause
Order by KEY_EMS, Calendar_Month_Code asc;

2) I have a feeling that there could be some issues with Null() stringed values on KEY_EMS. So i would do the following and on your load statement to make it selectable:

YourTable:
Load

      Equipment,

      System_Code,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      if(len(Key_EMS)< 1, "NO EMS KEY", Key_EMS) as Key_EMS

      Install_Flag ,

      Removal_Flag ,

      Technical_Start_Date

FROM (Connection)

Where clause

Order by KEY_EMS, Calendar_Month_Code asc;

3) Now i know that i don't have anymore empty strings, then the previous logic should work in here aswell with the previous script

YourTable:

Load

*,

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

if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 1, 0,

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

if(Previous(Install_Flag) = 0 and Previous(Removal_Flag) = 1, 0,

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

;
Load

      Equipment,

      System_Code,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      if(len(Key_EMS)< 1, "NO EMS KEY", Key_EMS) as Key_EMS

      Install_Flag ,

      Removal_Flag ,

      Technical_Start_Date

FROM (Connection)

Where clause
Order by KEY_EMS, Calendar_Month_Code asc;

Since i dont know what exactly is written in your 'Age' calculation, you need to add it aswell to the load script. It's not added right now.

[bharatkishore]

Hi K,

Thanks a lot for your help.

But i am not sure where to add the code.

Can you please help me where to add  in the attached file.