Skip to main content
E
emoreira14-Alexandrite
14-Alexandrite
January 21, 2018
Solved

Retrieving Datatable Row index

  • January 21, 2018
  • 1 reply
  • 4377 views

Hello All,

I need to make a query in a datatable in a time range and once I have the results I need to get the row index so I get the immediate previous entry, and sometimes the immediate next one too. I will try to represent it in the image below:

For example:

I want to query the events between 12:00 and 16:00. If I make a query to get me the results with startTime>12 and  and Time<16 it will return Events #3 and #4, when in fact I need to get partially the times in Events #2 and #5.

I considered querying the whole table into and infotable and then iterating it but it could be too much data. I also considered querying X hours before the startTime and Y hours after the end time, but this can be very flexible, so adding a couple of extra hours in the range might not solve.

The way I thought it could work is if I queried the range, then got the KEY of the first and last items in the result and queried them again in the original DataTable, now trying to return its row index, but I am not sure if it is possible. I did not find any OOTB service to do it.

Do you guys have any idea of how to do it without having to do a heavy query?

Cheers

Ewerton

Best answer by emoreira

Thanks Tanveer Saifee​, it seems it was a matter of getting the query condition right, not necessarily a specific TWX need. As I did not get the query conditions I thought I'd need some TWX API to retrieve the Datatable row index and them manipulate it..

BTW, the condition I still did not cover is when I search in a range when an event still did not finish, so there's no EventEnd information. I might need to add an OR condition to my query.

Thanks

Ewerton

1 reply

E
emoreira14-AlexandriteAuthor
14-Alexandrite
January 21, 2018

I solved the initial part of the quer y: using the BETWEEN statement only on the end date, but it still cuts the final portion:


      {
        "fieldName": "endTime",
        "type": "BETWEEN",
          "from": startTime,
          "to": endTime     
      }

I might do the same to the startTime and concatenate both Tables, but it will return many duplicate entries that I will have to filter out.

Cheers

Ewerton

T
tsaifee-214-Alexandrite
14-Alexandrite
January 21, 2018

Hello Ewerton,

is this a generic question or one specifically related to Thingworx functionality? I've not checked in relation to Thingworx, but if this is a generic question then this query should work:

EventStart < WindowEnd

AND

EventEnd > WindowStart

My assumption is that the Event data is valid, i.e. EventStart < EventEnd, if that holds then I think the query should always pick up Events which overlap the window.

For your example above:

WindowStart = 12

WindowEnd = 16

Event2Start = 11

Event2End = 12:30

etc.

E
emoreira14-AlexandriteAuthorAnswer
14-Alexandrite
January 22, 2018

Thanks Tanveer Saifee​, it seems it was a matter of getting the query condition right, not necessarily a specific TWX need. As I did not get the query conditions I thought I'd need some TWX API to retrieve the Datatable row index and them manipulate it..

BTW, the condition I still did not cover is when I search in a range when an event still did not finish, so there's no EventEnd information. I might need to add an OR condition to my query.

Thanks

Ewerton

Terms & ConditionsAccessibility statement