AnsweredAssumed Answered

PI AF table lookup attribute time format

Question asked by jasongogal on Dec 22, 2016
Latest reply on Mar 6, 2017 by Asle Frantzen

Hi all,

 

I am trying to configure a table lookup attribute to display some production data. Our facility ships a certain number of trucks per day. Each truck has a timestamp associated with it, which is recorded in a database. I've brought the data table containing the truck times into AF. The data column for the timestamp is shown below.

I am trying to build an attribute that I can use to display a stepped line chart for the number of trucks shipped in a day.  I set an attribute with a table lookup that returns the number of trucks shipped in a day when the time is less than or equal to whatever time the query is being performed. The table lookup that I'm using is SELECT Count(txtBOL) FROM Trucking WHERE txtBOL LIKE @Dayzero AND dteOperatingDateAndTime <= #%UtcTime%#;stepped=True

 

Dayzero here is just a variable created to get a string text like the BOL - 16.12.21.01 would be the first truck shipped on the 21st of December.

 

But the chart doesn't look right, and it looks even worse when I use Time instead of UtcTime. It seems to want to divide up the day in halves. What I want to see is more steps per day, instead of 2. If there are 16 trucks per day, I want to see 16 steps.

 

 

Digging a little deeper, the table lookup doesn't seem to correctly recognize the date and time in the table - see below - it interprets it as a string (?) and doesn't seem to get it into PI time. Attribute 1 returns the value of the time stamp with the BOL number that I have set in Attribute 3. It returns what looks like the correct time, 6:11 AM, but when I hover over it, it shows a 1970 time; AF doesn't seem to correctly recognize it.

 

I tried a couple different string builder expressions to get a date and time that AF can recognize, and kind of came up blank.

 

My shift is over now, but I thought I'd ask the group here to see if anyone has any ideas?

Outcomes