Retrieving Datatable Row index

[emoreira]

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

[emoreira]

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

[tsaifee-2]

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.

[emoreira · Accepted solution]

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

[emoreira]

I actually managed to get my query right, using a Nested query, as it is shown in the docs. I used the site JSON Editor Online - view, edit and format JSON online in order to get the Query in the right syntax. The only caveat is that you need to put your variable between double quotes so it does not show a formatting error msg.

Notice I have other fields too, so the query is big!

{
  "sorts": [
    {
      "fieldName": "startDate",
      "isAscending": true
    }
  ],
  "filters": {
    "type": "OR",
    "filters": [
      {
        "type": "AND",
        "filters": [
          {
            "fieldName": "A_B_Line",
            "type": "EQ",
            "value": "bocaID"
          },
          {
            "fieldName": "assetID",
            "type": "LIKE",
            "value": "assetID"
          },
          {
            "fieldName": "endDate",
            "type": "GE",
            "value": "startDate"
          },
          {
            "fieldName": "startDate",
            "type": "LE",
            "value": "endDate"
          }
        ]
      },
      {
        "type": "AND",
        "filters": [
          {
            "fieldName": "endDate",
            "type": "MissingValue"
          },
          {
            "fieldName": "A_B_Line",
            "type": "EQ",
            "value": "bocaID"
          },
          {
            "fieldName": "assetID",
            "type": "LIKE",
            "value": "assetID"
          }
        ]
      }
    ]
  }
}

Then at the end I check if the last entry is within the searched dates. If not, remove it:

if (result[result.length-1].startDate>endDate){result.RemoveRow(result.length-1)}

Tanveer Saifee   Thanks again for the help.