17 Replies Latest reply on Jul 7, 2014 1:35 PM by Gregor

    AF Linked Table timestamp

    tgreen

      Hi,

       

       

       

      In AF, we have various Linked Tables

       

      We have no issue creating template attributes to reference the data of the linked tables

       

      What I would like to set up is the 'Timestamp' showing in the Attributes of an Element to be the timestamp of that record

       

       

       

      Table structure is something like this

       

       

       

      DeviceName, Timestamp,  Value1, Value2, Value3, ect

       

      Attribute Template values are set to this:

       

       

       

      Value1

       

      SELECT Value1 FROM [Table] WHERE [DeviceName] = '%Element%' and Timestamp <= #%Time%# ORDER BY Timestamp desc

       

       

       

      Result is the correct value, but the Timestamp is '1/1/1970 12:00:00 AM' for all of them.  I want these to show essentially the 'Timestamp' of it's record found in the Linked Table

       

       

       

      Any ideas?

        • Re: AF Linked Table timestamp
          jnoss

          Hi,

           

          If you are using AF 2.5 you should be able to select the "Table provided time series data" option in the Table Lookup data reference and choose the Timestamp column to be the Time Column. That should then make the timestamp value of the values should the same as the timestamp in the column.

            • Re: AF Linked Table timestamp
              tgreen

              Yup, that was it

               

              Thanks!!

                • Re: AF Linked Table timestamp
                  fsaab

                  Hello,

                   

                  I am using the below query:

                   

                  SELECT Plan FROM PlanFigures WHERE ItemName = '%..\Element%' AND Timestamp = #%Time%#

                   

                  as i know, #%Time%# means current time , but i want to compare to today and not current time.can i use PI time syntax in the comparison when comparing a datetime column in a table to today for example.

                   

                  is there datetime functions i can use the sql query in the table lookup datareference.

                   

                   

                   

                  Best Regards,

                    • Re: AF Linked Table timestamp

                      Hello Francois,

                       

                      What you are asking for is an SQL Syntax question. You will find "tons" of examples in the internet. The following one just checks the "Timestamp" against the current day:

                       
                      SELECT Plan FROM PlanFigures WHERE ItemName = '%..\Element%' AND DATEDIFF(day, Timestamp, GETDATE()) = 0
                      

                       

                        • Re: AF Linked Table timestamp
                          fsaab

                          The issue is that when i use sql functions inside the settings "table lookup data refernece", i am getting function not defined error.

                          • Re: AF Linked Table timestamp

                            Hello Francois,

                             

                            After submitting above post I recognized you might be using PI OLEDB Enterprise Provider. With PI it's even easier referring to today. 

                             
                            SELECT Plan FROM PlanFigures WHERE ItemName = '%..\Element%' AND Timestamp >= 't'
                            

                             

                              • Re: AF Linked Table timestamp
                                fsaab

                                i have to write the query inside  the settings of  "table lookup data refernece".I am retrieving data from AF table into AF Element Atttribute.whe using time syntax like you are indicating above it will tell me cannot use = operator between datetime and string

                                • Re: AF Linked Table timestamp

                                  Hello once more,

                                   

                                  I just recognize that I become confused. You are talking about table lookup and it appears you are using PI OLEDB Enterprise Provider. This would mean you are taking a huge detour and makes me believe my understanding of what you are trying to do might be wrong. Can you please shed some light?

                                    • Re: AF Linked Table timestamp
                                      fsaab

                                      I am not using pi oledb enterprise provider at all :)

                                        • Re: AF Linked Table timestamp
                                          fsaab

                                          Hello Gregor,

                                           

                                          Execuse for the confusion, To clarify what i am doing:

                                           

                                          I am retrieving data from AF table into AF Element Attribute. so i am writing a query inside the settings of  "table lookup data reference".and i need to compare a timestamp column to "today"

                                           

                                          when using time pi syntax  it will tell me cannot use = operator between datetime and string.

                                           

                                          when using sql functions it will tell me sql functions undefined

                                           

                                          i am using AF 2.4

                                           

                                          Best Regards,

                                            • Re: AF Linked Table timestamp

                                              Hello Francois,

                                               

                                              Thank you for helping me off the jam pot. I don't believe what you are looking for can be done directly. What's needed is an "object" to compare against.

                                               

                                              I am not sure if there might be another approach but I succeeded with

                                               

                                              - Creating a PI Point serviced by the PI Performance Equation Scheduler with the following Equation: 't'
                                              - Adding an Attribute "Today" as a PI Point Reference to the point mentioned before
                                              - Referring the Attribute in the Where clause: ... AND Timestamp >= @Today

                                               

                                              Not what you are asking for but also a solution that you might want to consider is limiting the result set within the linked lookup table using the DATEDIFF approach.

                                                • Re: AF Linked Table timestamp
                                                  Marcos Vainer Loeff

                                                  Hello Francois,

                                                   

                                                  If you take a look at the Release Notes of PI AF 2.5, you will find enhancement 13861: "The Table Lookup Data Reference can now be configured to support retrieving time-series data based solely on a time column within the AF Table."

                                                   

                                                  Therefore, the first thing you should do is to update your PI AF to version 2.5 at least, as pointed out by John. Nevertheless, I recommend you to update to version 2014 (2.6) or 2012 SP2 (2.5.2) which has many known issues fixed.

                                                   

                                                  Hope this helps!

                                                    • Re: AF Linked Table timestamp
                                                      fsaab

                                                      Hello Marcos,

                                                       

                                                      The Upgrade to 2.5 helped me . after checking "Table provided time series data" i was trying  to put condition in the select statement to select rows in specific time.but it seems this works automatically without putting the condition explicitly.because when i show the value in process book and i change the date the dynamic value goes and retrieves a different row in the table  with a different date.

                                                       

                                                      one question for knowledge and for future projects maybe: what if i want to compare the timestamp column  to dates explicitly,  it seems the only options are the one in the drop down list between %% like %Time%.

                                                       

                                                      best,

                                                    • Re: AF Linked Table timestamp
                                                      fsaab

                                                      Hello Gregor,

                                                       

                                                      Thanks for your answer.This could be a solution  but i am having limitation on creating pi tags.I preferred to use the new feature in AF 2.5 and this resolved the issue as stated in this thread.

                                                       

                                                      Best & Regards,

                                                        • Re: AF Linked Table timestamp
                                                          Marcos Vainer Loeff

                                                          Hi Francois,

                                                           

                                                          Could you provide more details about what do you want to compare. Do you want to make the comparison within the query or betweeen values of attributes?

                                                            • Re: AF Linked Table timestamp
                                                              fsaab

                                                              Hi Marcos,

                                                               

                                                              i am talking within the query that i write in the settings of the "Table Lookup" Data Reference.

                                                               

                                                              what if i want to retrieve rows based on a certain time condition. what to use in the where clause:

                                                               

                                                              SELECT Plan FROM PlanFigures WHERE ItemName = '%..\Element%' AND Timestamp = *-3d

                                                               

                                                              what if i want to show previous 3 days data in a certain AF attribute using the above query.

                                                               

                                                              Thanks,

                                                                • Re: AF Linked Table timestamp

                                                                  Hello Francois,

                                                                   

                                                                  You can either use a PI Point data reference to a point of type Timestamp or form your lookup table query accordingly.

                                                                   

                                                                  "Table provided time series data" allows you to trend the data originating from your lookup table and I understand this is what you've been looking for. Users can specify start- and endtime of the trend period. So why would you want to limit the data within the PI Point Data reference? Do you have a specific use case for this requirement?