Follow

Integration Configuration - Value Query with Event Context

When integrating measure values using event context, the following placeholders can be used in database queries to insert or update data:

  • [ModelAttribute(“ModelAttributeName”)]
This will be replaced by the value of the model attribute configured on the measure based on the name of the model attribute. If no value is found, it will be replaced by a blank string (“”).
  • [measuredescription]
This will be replaced by the value of the description of the measure. If no value is found, it will be replaced by a blank string (“”).
  • [measureintervaltype]
This will be replaced by the string value of the measure’s interval type (Minutely, Hourly, Shiftly, Daily etc.). If no value is found, it will be replaced by a blank string (“”).
  • [measureparent]
This will be replaced by the string value of the name of the measure’s parent entity. This can be either a metric or an event. If no value is found, it will be replaced by a blank string (“”).
  • [measureid]
This will be replaced by the integer value of the measure’s unique ID.
  • [measurename]
This will be replaced by the string value of the measure’s name. If no value is found, it will be replaced by a blank string (“”).
  • [measureuom]
This will be replaced by the string value of the measure’s unit of measure that has been configured. If no value is found, it will be replaced by a blank string (“”).
  • [hierarchicalname]
This will be replaced by the string value of the measure’s hierarchical name up-to metric of event level. If no value is found, it will be replaced by a blank string (“”).
  • [measureeventperiodvalue]
This will be replaced by the double representation of the measure’s value for the event period. If no value is found, it will be replaced by NULL (“NULL”)
  • [measureeventperiodvaluedatetime]
This will be replaced by the most recent timestamp the measure value had been changed.
  • [measureeventperiodvaluedatetimeutc]
This will be replaced by the most recent timestamp, in UTC, the measure value had been changed.
  • [periodend]
This will be replaced by the Date Time value of the current time period’s end time being processed.
  • [periodstart]
This will be replaced by the Date Time value of the current time period’s start time being processed.
  • [periodendutc]
This will be replaced by the UTC Date Time value of the current time period’s end time being processed.
  • [periodstartutc]
This will be replaced by the UTC DateTime value of the current time period’s start time being processed.
  • [quality]
This will be replaced by the integer representation of the measure’s value quality score, based on the OPC quality code as received from the data source for that time period interval.
  • [version]
This will be replaced by the integer representation of the current version of the value.
  • [version]
This will be replaced by the integer representation of the current version of the value.
  • [preferredevent]
All values will be pushed to the consumer. This include all versions, not just the latest value. The preferred flag will be set as a Boolean (true/false) to indicate if the current event in the value set is the preferred event or not.
  • [usernameid]
If the current version had been changed manually by a specific user, the username ID will be replaced with the unique ID of the user configured in Flow that modified the value. If no user had made changes to the value, it will be replaced by a blank string (“”).
  • [EventAttribute(“EventAttributeName”)]
This will be replaced by the value of the event attribute configured on the measure, based by the name of the attribute. If no value is found, it will be replaced by a blank string (“”).
  • [eventperiodid]
This will be replaced by the integer representation of the unique event value ID of the specific event period interval.
  • [measureeventperiodvalueid]
This will be replaced by the integer representation of the unique measure value ID of the specific event period interval.
   

Microsoft SQL Example

  update ERPStaging set Value=[MeasureEventPeriodValue] where DateTime='[PeriodEnd]'
IF @@ROWCOUNT=0
  insert into ERPStaging (DateTime, Value, tagname, EventPeriodId, MeasureId) values
  ('[PeriodEnd]',[MeasureEventPeriodValue], '[MeasureName]', [EventPeriodid], [MeasureID])

MySQL Example

  update ERPStaging set Value=[MeasureEventPeriodValue] where DateTime='[PeriodEnd]'
IF @@ROWCOUNT=0
  insert into ERPStaging (DateTime, Value, tagname, EventPeriodId, MeasureId) values
  ('[PeriodEnd]',[MeasureEventPeriodValue], '[MeasureName]', [EventPeriodid], [MeasureID])

PostgreSQL Example

do $$
begin
  update ""schema"".""ERPStaging"" set""Value"" = [MeasureEventPeriodValue] where
  ""DateTime""='[PeriodEnd]' and ""MeasureId"" = [MeasureID]
IF NOT FOUND THEN
  insert into schema.""ERPStaging"" (""DateTime"", ""Value"", ""Tagname"", ""EventPeriodId"",
  ""MeasureId"") values ('[PeriodEnd]', [MeasureEventPeriodValue], '[MeasureName]',
  [EventPeriodid], [MeasureID])
end if;
end
$$
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments