locked
I literally need the latst value RRS feed

  • Question

  • Hi

    My stream data is pretty standard:

    [DateTime],[DeviceNo],[Value],[Longitude],[Latitude]

    2018-07-25 00:01,DEV1,28,-38,198

    2018-07-25 00:01,DEV2,32,-38,199

    2018-07-25 00:02,DEV1,30,-38,198

    2018-07-25 00:02,DEV2,31,-38,199

    2018-07-25 00:03,DEV1,29,-38,198

    2018-07-25 00:03,DEV2,33,-38,199

    See how the data for DEV1 (devcice 1) across 3 minutes shows value changes from 28, to 30, to 29. And how the data for DEV2 (device 2) across the same 3 minutes shows value changes from 32, to 31, to 33.

    My final output is a live Power BI Dashboard. One visual shows the data on a normal timeline and this can be done with a simple Stream Analytics query such as:

    Select * into OUTPUT from INPUT

    But I also need to show just the latest value for each device. In other workd for device 1 this would be 28, then replaced with  30, then replaced with 29. And similar for device 2.

    Any idea as to how this can be achieved?


    'If this was helpful, please mark and an answer or as being helpful'

    Wednesday, July 25, 2018 10:56 AM

All replies

  • You can use a separate query with max function on first column and group it by Dev categorization column.

    Wednesday, July 25, 2018 11:18 PM
  • If I understand correctly, you want a real time Power BI dashboard fed by ASA job? An example of this is in this tutorial: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-manage-job#start-the-job-and-visualize-output

    If you are looking to have 2 lines in your Power BI graph which change in real time (one for dev1 and another for dev2), then instead of a Select *, you should group by device number.

    Thursday, July 26, 2018 5:07 PM