I have a series of tags that I need to find the last non-zero value for, how do I go about this using DataLink?
Suppose that you expect the last non-zero tag to have occurred within the past month. In PI DataLink, import all events in the past month using Compressed Data. Set the start time to be * and the end time to be *-1mo. By reversing the start time and the end time, you force the results to be sorted from latest to earliest. For the filter expression, use 'YourTag' <> 0. Click OK and copy the formula used to get the results. It should have the form =PICompDat(…). In a new cell, use the formula =INDEX(PICompDat(…),3,1). This new cell contains the last non-zero value. You can now delete the array created by PI DataLink, since we needed it only to obtain the PICompDat(…) formula.
INDEX returns the value in an array given a row number and a column number. In the PICompDat(…) array, the latest non-zero value is in the 3rd row of the 1st column.
Retrieving data ...