Ignition Dataset using "view" expression from simulation tools

Ignition
1

Disclaimer--Newbie to ignition and scripting:
I am attempting to use the View Expression from simulation tools module to query a historical tag that is stored as a discrete value, in this case, a batch number. I want to retrieve the timestamp that indicates the start of the batch and the end of the batch and use them in a powerchart as the start and end date to allow for charting any number of stored historical attributes that might be associated with that batch.

The historical data looks like this:
Time                          Batch
4/25/2023 22:18        1145655
4/25/2023 23:58        1145655
4/26/2023 1:39          1145655
5/4/2023 1:10            1146131
5/4/2023 2:51            1146131
5/4/2023 4:32            1146131

I am attempting to use the following binding transform against a dataset retrieved from a historical binding indicated as {value} below:

view("Select min(t_stamp) As Start, max(t_stamp) As End, batch as Batch Group By batch",{value})

Start                         End                       Batch   
4/25/2023 22:18       4/24/2023 2:37     1145655
5/4/2023 1:10	         5/4/2023 4:32	      1146131

I want to include a “Where batch = specific batch from a drop down property
Example: {.../Header/Details/Batch/BatchNum.props.text}” but not sure how to use the ARG’s option?

**--Desired Final Result--**
Start                         End                       Batch   
5/4/2023 1:10	       5/4/2023 4:32	    1146131

Current Results So Far:
Start                            End                                  Batch
1683186524207         1683186524207                [u'1146131']

Any help is appreciated!

2

It would look something like this:

view("Select min(t_stamp) As Start, max(t_stamp) As End Where batch==args[0]",
  {value}, {path.to.batch.dropdown.prop})

The args variable is assigned a jython tuple of all of the function arguments after the implicit FROM dataset.

{ When posting code or logs or other text info into a topic, highlight it after pasting, and click the "PreFormatted Text" button in the comment editor toolbar. You can click the pencil icon to go back and fix things you've already posted. }

1 Like
3

Thank you for the guidance!
One last question is the formatting of the batch number comes back with the following: [u'1146131'] how can this be kept or formatted in original format as a string without brackets, u, or ' ? The original tag value that is stored in the historian is a string. I can use script to strip out the value if that is the only choice but just trying to better understand what I am seeing.

Thanks again.

4

Two things are happening here:

  • When you use grouping in view() output columns are lists, not singletons. If you hadn't included the AS clause for batch, view() would have substituted batch[0] for you to get the singleton. When you use AS, you have to be explicit. Take a closer look at the grouping section of the docs.

  • The u'...' is an artifact of converting a unicode string for display with repr(), which is invoked for list elements when converting a list to a string. Those delimiters aren't really present.

{Notice that my suggestion dropped batch as an output column because filtering by the same value as grouping yields just the one output row with that value.}

5

Thank you that clears things up. However, the Min(t_stamp) and Max(t_stamp) columns bring back the same timestamp value?

Start                                       End
`1683186524207         1683186524207 `

.

6

Hmm. I omitted the Group By batch from my example. Did you put that back in?

7

No below is what I have:

view("Select min(t_stamp) As Start,max(t_stamp) As End Where batch==args[0] Group By batch",{value}, {.../Header/Details/Batch/BatchNum.props.text})

8

Please use the "Preformatted Text" button, not the quotation button, when pasting code.

9

I believe I tracked it down to the primary dataset Historical Query

Thanks for your help