Skip to main content
darkoj
darkoj
August 17, 2021
Question

How to check specific range of values in Array.

  • August 17, 2021
  • 17 replies
  • 1 view

The issue that I'm experiencing is that I have an list of dictionary's.
The structure of dictionary's is like

{ date: 03/05/2021,  code: "113", amount: "0", id: 4001 }, - END DELAY

{ date: 01/05/2021,  code: "110", amount: "234", id: 4002 }, - DELAY

{ date: 22/04/2021,  code: "110", amount: "234", id: 4003 }, - START DELAY

{ date: 18/04/2021,  code: "113", amount: "0", id: 4004 }, - END DELAY

{ date: 13/04/2021,  code: "110", amount: "234", id: 4005 }, - DELAY

{ date: 09/04/2021,  code: "115", amount: "0", id: 4006 },

{ date: 03/04/2021,  code: "110", amount: "234", id: 4007 }, - START DELAY

{ date: 18/02/2021,  code: "113", amount: "0", id: 4008 }, - END DELAY

{ date: 22/01/2021,  code: "110", amount: "234", id: 4009 }, - DELAY

{ date: 22/11/2020,  code: "105", amount: "0", id: 4010 },

{ date: 03/05/2020,  code: "103", amount: "0", id: 4011 },

{ date: 13/01/2019,  code: "110", amount: "234", id: 4012 }, - DELAY

{ date: 03/05/2016,  code: "110", amount: "234", id: 4013 }, - DELAY

... and so on.


I need to calculate delays in contract from this between code 113 and code 110 for every range as explained above (colored values).

Event (code) 110 means delay.
Event (code) 113 means the end of the delay.

Before reporting the end of delays (code=113 ), a contract can have more reported events (code) 110 (event is reported at least once a month)
One contract can have different long delays. Within the contract, I need to find the longest delay in 1 year.
I need to find all events (code) 113 in 1 year (365 days) and all related events 110 (the last events 110 is not necessarily to be within 1 year (it can be more than 1 year)

What is the best way to do this? How can I identify the ranges that I will know what is the starting "113" and ending "110". Thx.

17 replies

csteward
csteward
August 17, 2021

This is interesting logic to create rules for.. Just to confirm, this data is not stored in a database?  Typically I would do this type of calculation within a view on the DB side.

darkoj
darkojAuthor
August 17, 2021

Basically this data is returned from integration call with complex structure and stored in DB later on so just to answer your question, Yes it is stored. Any idea about this logic? 

csteward
csteward
August 18, 2021

If you can calculate this based on your data store, that would be my preference.  This could be done in Appian however, with a little additional overhead. 

This an example with MSSQL, over table chris_test_table (id,date,code,amount)

SELECT
[id]
[date],
[code],
[amount],
[delayStart],
DATEDIFF(
	day,
	[delayStart],
	[date]
) 'daysDelayed'
FROM
(
	SELECT
	t.[id],
	t.[date],
	t.[code],
	t.[amount],
	CASE -- Return minimum 110 that is after the prior 113
		WHEN (t.[code] = 113) THEN -- End of Delay
			(
				SELECT min(date) from chris_test_table t2 where t2.date= 
				CASE -- If no prior 113s, return first 110
					WHEN EXISTS (select * from chris_test_table t3 where t3.date

This could be expanded as necessary and tied to a CDT in your application for easy access.

The output puts the final delay details on the lines with the 113 / closing event as:

davel001150
davel001150
August 18, 2021

Let's see.

Perhaps use wherecontains to get the index of every 113.  In example above you would have {1, 4, 8}.  Put that in a local variable.

Now, break up the list into a list of lists, {{1,2,3},{4,5,6,7},{8, 9, 10, 11, 12, 13}}.  Put that in a local variable.

Then forEach sublist, index(max( wherecontains(code 110))) That should get you {3, 7, 13 [if that's the end of the list for that one]} Put THAT in a local variable.

Now, for each in {1, 4, 8}, subtract date at that index from date at {3, 7, and 13}  Now, you'll have a list of time intervals.  Wrap this last forEach in a max() function, should have it.  May be ways of tweaking this to work more efficient, but it's how I would start.

darkoj
darkojAuthor
August 18, 2021

Do you have an idea how to break up the list that doesn't have a fixed value for the number of elements into a list of lists ( {{1,2,3},{4,5,6,7},{8, 9, 10, 11, 12, 13}} ) as you suggested? As this was just an example, events 113 and 110 can occur anytime and as many times I wont have same structure everytime so, we again return to the original question how do I know what is the end and star of my list/lists/chunks of data? 

davel001150
davel001150
August 18, 2021

a!localVariables(
  local!list: {1001, 1015, 1023, 1025, 1048, 1052, 1067, 1085, 1093, 1099, 1100, 1120, 1130},
  local!indeces: {1, 5, 11},
  a!forEach(
    items:local!indeces,
    expression: if(
      fv!islast,
      index(
        local!list,
        enumerate(length(local!list) - fv!item + 1) + fv!item
      ),
      index(
        local!list,
        enumerate(local!indeces[fv!index + 1] - fv!item) + fv!item
      )
    )
  )
)

Terms & ConditionsAccessibility statement