15 Replies Latest reply on Nov 19, 2014 1:24 PM by binod_tiwary

    How to get current date in PI AF formula?

    binod_tiwary

      Hi,

       

      I am very new to PI so please excuse me if this is something very obvious.

       

      My requirement is very simple, I am trying to set the value of an AF attribute by using a 'Formula'. My formula will look like below as I need to return a pre defined value depending on the current month:

       

      If CurrentMonth="April" Then 100 else if CurrentMonth="May" Then 200 else if CurrentMonth="June" Then 800 ..............and so on.

       

      My problem is how to get the current date (is there something equivalent of SQL server GetDate() function in Pi AF) which I can use to work the current month use in the formula.

       

      Any suggestion please. If any alternate approach should be taken

       

       

       

       

       

       

        • Re: How to get current date in PI AF formula?

          Hello Binod,

           

          Welcome

           

          I am not sure if what you like to do here works with a Data Reference formula because they appear to not understand equations like month('*').

           

          You can however use Asset Based Analytics and write the Analysis result to an AF Attribute. Please review the following Formula:

           

           

           
          If (Month('*')=10) Then 100 Else 0
          

           

            • Re: How to get current date in PI AF formula?
              Roger Palmen

              In the past i created a custom datareference to deliver datetime values, based on e.g. the query time. E.g. "BOM" would return the timestamp at the beginning of the month for a given query time. Quite easy to build in C#

               

              But for your example, try this approach: Use a lookup table to resolve the value, You can use the %time% substitution parameter to get the current time context into your query.

                • Re: How to get current date in PI AF formula?
                  binod_tiwary

                  Hi Roger, Thanks for your response. This option is sounding good to me but could please elaborate little bit or point me to some doc which I can refere. I am not quite following what do you mean by %time% substitution parameter. My ultimate aim is to set the value of an AF attribute from a table lookup. Table holds values for each month of a year and I need to select the value for present month but unfortunately I am not able to find out how to get the present month value so that I can use it the lookup query. Any help would be really helpful.

                • Re: How to get current date in PI AF formula?
                  binod_tiwary

                  Hi Gregor, Thanks for your response but unfortunately I can't use Asset Based Analytics :-(

                    • Re: How to get current date in PI AF formula?
                      Bannikov

                      Hello Binod,

                       

                      which version of AF are you using?

                        • Re: How to get current date in PI AF formula?
                          Roger Palmen

                          Hi Binod,

                           

                          It turns out my solution for using a looup table does not work. I assume the comparison operators don't work on datetime values.

                           

                          I tried this query for a Table Lookup attribute (returning first matching row):

                           
                          SELECT MonthName FROM MonthNameLookup WHERE StartDate >= #%Time%# ORDER BY StartDate
                          

                          Where table MonthNameLookup has two columns: MonthName (string) and StartDate (datetime). The table contents would have one datetime value for every month the system would need to cover (yes, each year... i know it's ugly, but it's simple and effective), using the first timestamp in the month, e.g. July 1, 2014 00:00 for the entry for July.

                           

                          I did try on numerical values, and that does work. Can someone confirm if the comparison operators in the Table Lookup DR are constraint to numeric values?

                           

                          I really throught i did use datetime lookups before...  Let me do some more research!

                          • Re: How to get current date in PI AF formula?
                            binod_tiwary

                            Hi Sergey,

                             

                            I am using version 2012.

                              • Re: How to get current date in PI AF formula?
                                Roger Palmen

                                In the end it did work. Mixed up day and month in my lookup table...

                                 

                                I did fix the query to:

                                 
                                SELECT MonthName FROM MonthNameLookup WHERE StartDate <= #%Time%# ORDER BY StartDate DESC
                                

                                 

                                  • Re: How to get current date in PI AF formula?
                                    binod_tiwary

                                    Hi Roger,

                                     

                                    Many thanks, this is what i needed. I have changed my table layout little bit so that now it holds three columns (SiteName, MonthlyTarget, EffectiveDate)

                                     

                                    Data for a site in the table looks like below:

                                     

                                    SiteName    MonthlyTarget EffectiveDate

                                     

                                    xyz     100     01/01/2015 00:00:00

                                     

                                    xyz     200     01/02/2015 00:00:00

                                     

                                    xyz     300     01/03/2015 00:00:00

                                     

                                    xyz     400     01/04/2014 00:00:00

                                     

                                    xyz     500     01/05/2014 00:00:00

                                     

                                    xyz     600     01/06/2014 00:00:00

                                     

                                    xyz     700     01/07/2014 00:00:00

                                     

                                    xyz     800     01/08/2014 00:00:00

                                     

                                    xyz     900     01/09/2014 00:00:00

                                     

                                    xyz     1000     01/10/2014 00:00:00

                                     

                                    xyz     1100     01/11/2014 00:00:00

                                     

                                    xyz     1200     01/12/2014 00:00:00

                                     

                                    and the select the MonthlyTarget value for present month is looking like:

                                     

                                    SELECT MonthlyTarget FROM Table WHERE SiteName = 'xyz' AND EffectiveDate <= #%Time%# ORDER BY EffectiveDate desc

                                     

                                    Now the disadvantage is my table looking highly de-normalized and we need to update two columns (MonthlyTarget and EffectiveDate) every year but as it's saving me from writing lots of c# code including if- else statement, I'm going with this option.

                                     

                                    Thanks again :-)

                                      • Re: How to get current date in PI AF formula?
                                        Roger Palmen

                                        Yes, highly denormalized! That's fashion in modern big data! 

                                         

                                        Do keep in mind that you need a process that will update this table to include monthly targets for future months / years.

                                          • Re: How to get current date in PI AF formula?
                                            binod_tiwary

                                            Hi Roger, Hope you are doing good. I have ended up in another problem where I think you can help please.  When I am trying to read the attribute value( which is configured as table lookup using time substitution as per your suggestion above) over a time range I am not valid AfValue. The error I am getting is :          

                                             

                                            AFValue="Data was not available for attribute 'Target': Data was not available for attribute 'Target'." UOM=null Timestamp={17/11/2014 13:30:00} IsGood=false     OSIsoft.AF.Asset.AFValue

                                             

                                            I am trying to read the data using following code:

                                             

                                            var afValues =

                                             

                                                               afAttribute.Data.InterpolatedValues(

                                             

                                                                   new AFTimeRange(startTime.ToUniversalTime(), endTime.ToUniversalTime()),

                                             

                                                                   new AFTimeSpan(TimeSpan.FromMinutes(30)), null, null,

                                             

                                                                   true);

                                             

                                            It is making sense to me why I am getting 'Data was not available for attribute' error as time substitution query just work current time but I am trying to read data for the past time range. Could you suggest any solution to overcome this problem? I need to plot a graph for this attribute that's why I am trying to read it's value over a time range. Please let me know if I am not sounding clear.

                                             

                                            Thanks

                                             

                                            Binod

                                              • Re: How to get current date in PI AF formula?
                                                Roger Palmen

                                                Getting Interpolated values should work, you can test that using the time-series data funtion, on the sampled tab. The PlotValues call does not work however, and this will give you the value at the start of the timerange for the entire time range, which is not what you need. Without digging into the reasons for that (don't exactly know why), i'll take a closer look at your example you gave on your post of Oct. 27.

                                                 

                                                You state that the table "it holds three columns (SiteName, MonthlyTarget, EffectiveDate)". That is in effect a time-series dataset, as you have an EffectiveDate which is like a timestamp. That can be plotted by using the time-series functionality of the Table Lookup. Any attributes derived from that (e.g. formulas) should also be capable of plotting.

                                                 

                                                I don't see any obvious issues in the call you make. I'd recommend to check the Time Series data function in PSE to check if it returns the results you expect, so you know if the problem is in your code or in your AFmodel.  If PSE does return a value at the timestamp the error shows, there is an issue in your code, else the issue is in your AFmodel & data