New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Resource Advisor - Performance Analytics Forum
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Using an expression to patch small sections of data

The recipe is part of the Calculated Measurements Cookbook.


If for some reason you either can't or don't want to edit values in the database, it's possible to supercede small stretches of data using an expression in a way that doesn't alter saved values.


There is a function "TvPair()" that creates small sections of data that look to the rest of PAM like they came from the database.  There's also a seemingly useless aggregation function in the calc engine called 'FIRST' which winds up being quite handy.


FIRST(A, B, C,…) takes any number of streams and for each interval, it searches the arguments left to right and returns the value from the first stream that's not null.


Use TvPair() to provide the patch values.  They don't need to be contiguous, and can span any range of dates though they do need to be in increasing date order.  Use that as the first argument to FIRST(), and the stream that you want to patch as the second argument, like


FIRST( {TvPair( ... )}, [Electricity:ENERGY][kWh])


Here's a working example:


{TVPair([[TimestampPosition => IntervalEnding]][[IntervalSize=>15]]
3/13/2018 0:15 99
3/13/2018 0:30 101
3/13/2018 0:45 99
3/13/2018 1:00 101
3/13/2018 1:15 99
3/13/2018 2:15 201
3/13/2018 2:30 99
3/13/2018 2:45 101
3/13/2018 3:00 99


This overrides the values of the electricity stream on the morning of March 13 with some recognizable values: 


03/13/18 00:15:00 -07:0099
03/13/18 00:30:00 -07:00101
03/13/18 00:45:00 -07:0099
03/13/18 01:00:00 -07:00101
03/13/18 01:15:00 -07:0099
03/13/18 01:30:00 -07:00201.5625
03/13/18 01:45:00 -07:00196.875
03/13/18 02:00:00 -07:00201.5625
03/13/18 02:15:00 -07:00201
03/13/18 02:30:00 -07:0099
03/13/18 02:45:00 -07:00101
03/13/18 03:00:00 -07:0099
03/13/18 03:15:00 -07:00210.9375
03/13/18 03:30:00 -07:00253.125
03/13/18 03:45:00 -07:00271.875
03/13/18 04:00:00 -07:00281.25
03/13/18 04:15:00 -07:00304.6875
03/13/18 04:30:00 -07:00328.125
03/13/18 04:45:00 -07:00309.375
03/13/18 05:00:00 -07:00314.0625


Notice that the timestamps in the TvPair have a hole in them - there are three missing values between 1:15 and 2:15 during which the original underlying stream shows through.  There's nothing to stop you from patching a single value this way, or a handful of values scattered through a longer time period.  You only need to make entries in the TvPair() list for the ones you want to override.


About those timestamps: this one happens to use the US format mm/dd/yyyy, which is accepted, but it's usually preferrable to use the unambiguous yyyy-mm-dd.  Timestamps are assumed to be in the TZ of the hierarchy location, but you make a timestamp UTC by adding 'Z' after the time.  You can also specify a UTC offset explicitly by adding '+' or '-' followed by hh:mm after the time, like


2018-03-18 04:45:00 -07:00


for UTC-7.  Generally, TvPair() is set up to accept timestamps and values pasted from Excel.  You may supply commas if you have some CSV around that you want to use, but they aren't necessary and will just be ignored.


Though the purpose is quite different, this is very similar to 'data fall-back' described in