• Replies 13 replies
  • Subscribers 22 subscribers
  • Views 81 views
  • Users 0 members are here
Options
Related

IF tagval is NULL Then NULL

RayHoward

Hi,

In PI I have a set of LIMS results that come in every three hours and I want to average these over the 24 hours. When all the tags are in, the Average function (in Analyses) works fine.

If one of the data points is NULL (due to any reason) the value returned by the TagVal expression returns the value prior to the actual one I need.

So, two questions. One, how do I get the value to return a value at an exact time (I have this working in Datalink), if the value is NULL, show NULL, ELSE show value.

 

Second question, can I do an IF((tag) IS NULL THEN NULL ELSE 'tagval','*-6h')). If I put a 0 then the TagAvg is going to be wrong.

 

Many thanks.

Parents Reply Children
  • in reply to skwan

    What are you trying to do with this?

    IF((tag) IS NULL THEN NULL ELSE 'tagval','*-6h'))

    If the value is NULL, then leave it as NULL, else you want the value from 6 hours ago?  What's the rationale for this?

  • in reply to skwan

    Hi Stephen,

    The tag I'm using is very simple: TagVal('211_FCONFe.result','y+8h'). We have results every 3 hrs, so we have y+2h, y+5h etc. We also have 212_FCONFe.result, 213_FCONFe.result etc. The tags are defined with Step Value = On and Compressing = Off. I don't want any of the results to be interpolated so I believe this is the way to stop this. The end result is I want to do a Weighted Average Grade across the Total CON Production x Grade, so CON per line x Average Grade per line (per 24hrs). If the result interpolates and brings back (or interprets) a value that should be NULL then the WAG value is wrong. If the grade is NULL I don't want the calc to be weighted by a 0 or a wrong (interpolated) value. The end result is to calculate CON x Avg Grade (per line) which gives a 'metal' content, then SUM those and divide by total CON. That will give me the daily WAG grade across the lines.

     

    This is why I thought if I could do a IF NULL.

     

    Hope it makes sense. Using PI System Explorer 2016 R2 SP1.

     

    Ray 

  • in reply to RayHoward

    Hi Ray,

    The issue with the NULL is that there's no way for the software to know whether there's a NULL value or not.  We as humans know that we expect a value at this time, but since there is none, it's a NULL.  Meanwhile all the software knows is that at this time, it is holding a value (the last value) in memory.  As an example, at 2am, there's a value of 10, which is held in memory, at 5am, the value 10 is still being held in memory.  The software doesn't know that there should've been a new value and didn't get one.  It only knows there's a value of 10.

     

    I can think of maybe 3 ways you can do this.

     

    First, if you have the ability to upgrade to the 2018 or newer release, there are a couple of new functions that you can use to do this easily.  Every 24 hours, you can do a RecordedValues() function call to get all the actual values from the last 24 hours (8 or less values, you don't have to worry about NULL).  Then apply an event weighted SUM or AVG on these returned values.  This is a one line expression in an analysis.

     

    Another way is to check the timestamp of each value every 3 hours, then write the valid values to another PI Point.  Then every 24 hours do an event weighted summary on this new PI Point.

     

    Lastly you can perform a calculation every 24 hours and look for the previous value 8 times using PrevVal() and check their timestamps.  Weed out all the ones that you don't want then do an event weighted summary on these values.

  • in reply to skwan

    Thanks Stephen,

    We are in the process of the upgrade, but I'm not holding my breath for it. I think your response is similar to what Jim suggested which is an option I will try today.

    I'm sure I'm not the first person to want to do this, so I will report back if it works.

    Many thanks,

    Ray

  • in reply to RayHoward

    Ray,

     

    Just be aware that there is not a function to check for NULL because in essence you did not receive an update.  See my explanation above.  Consider checking value and timestamp to determine if you have received an updated value or not.

  • in reply to skwan

    I am going off the assumption that instead of "10" that the LIMS value truly is the word "NULL"

     

    If there is no new value, then tagmean should be the correct function since it only goes off of actual values and doesn't take time into consideration. If the value is "0" and a timestamp is given and that is considered "NULL", then all bets are off. 

  • in reply to Jim Gavigan

    Hi Jim,

    When there is no sample, there is no row in the table. We would just miss the 05:00 one for example and go from the 02:00 to 08:00 in Archive.

    Jim, you might know the answer to my second part of the question:

    Setting Step Value to ON and Compressing to OFF. Will that have any influence on it?

  • in reply to RayHoward

    Take a screen shot and post it here from archive editor where a sample was NULL or got skipped because there is no result.