PI DataLink CompressedData FormulaArray problem

Question

I want to retrieve compressed data to Excel 2013 trough PI DataLink 2017 SP1.

To avoid bad values and duplicate values i made expression.

If badval('Tag') then 0 else if tagval('Tag') = prevval('Tag') then 0 else tagval('Tag')

This expression works well in some rows and gives desired results, but in some rows without rules shows following error:

pastedImage_0.png.png

Formula in Excel top line...

=PICompFilDat('From PI #2'!$C$27,'From PI #2'!$B$11,'From PI #2'!$B$12,"if tagval('Tag') = prevval('Tag') then 0 else If badval('Tag') then 0 else tagval('Tag')",0,11,"","inside")

Where is the problem and how to solve it?

Regards,

Igor

Community User · Accepted Answer

Hi Igor,

I believe this is an Excel issue with the length of the array formula used to produce the results of your DataLink query. Excel array formulas have a maximum length of 255 characters as far as I know. It looks like you have embedded the filter expression into the DataLink formula (by typing it directly into the filter expression field in the PICompDat function sidebar. If this is the case, I would suggest that you place the filter expression in a cell on the spreadsheet and then reference the cell address in the DataLink function for your filter expresssion and see if that resolves the issue.

John