Data modelling: find duration of range

[SteveAlstead]

Hi all,

I have a question about my data. We have sensors in our office. These sensors measure every 10 seconds if there is anyone in a room. These sensors deliver data with for every sensor per 10 seconds a row. 

We want to analyse this data which looks like this:

Sensor ID	Detection	DateTime
1000	0	5/1/2023 10:00:10
1000	1093837	5/1/2023 10:00:20
1000	838773	5/1/2023 10:00:30
1000	0	5/1/2023 10:00:40

So in the first column you find the Sensor ID. In the second column you'll find a random number when there is a detection else 0. Last column shows you date/time of detection. 

Now I want to transform this table into:

Sensor ID	First detection	Last detection
1000	5/1/2023 10:00:20	5/1/2023 10:00:30
And so on

If it's possible to transform the data into this format I can easily analyse the data. 

Any idea's how to fix this?

Sample data: https://we.tl/t-QmD7rei7K8

Kind regards,

Steve

[amitchandak]

@SteveAlstead , Try measures like

First detection = Minx(filter(Table, Table[Detection] <>0) , [DateTime])

Last detection = Maxx(filter(Table, Table[Detection] <>0) , [DateTime])

[SteveAlstead]

Hi, 

Thanks for your reply! The problem is that there are per sensor per day lot's of detections. See my sample data for an example. So I think it's not that easy using iterators to fix this problem. Or could you maybe show an example how you think to solve this problem?

Kind regards,

Steve