CF to index a TimeStamp List

[dwdata]

I have a CF that builds a list of TimeStamp intervals:

1/6/2018 9:00 AM
1/6/2018 9:10 AM
1/6/2018 9:40 AM
1/6/2018 9:50 AM
1/6/2018 10:40 AM
1/6/2018 10:50 AM
1/6/2018 11:00 AM
1/6/2018 11:40 AM
1/6/2018 11:50 AM
1/6/2018 12:00 PM
1/6/2018 12:10 PM
1/6/2018 12:20 PM
1/6/2018 12:30 PM
1/6/2018 12:40 PM
1/6/2018 12:50 PM

I want to analyze this list and generate an index sequence (resetting the index back to 1 if there is a break in sequence)

1/6/2018 9:00 AM = 1
1/6/2018 9:10 AM = 2
1/6/2018 9:40 AM = 1
1/6/2018 9:50 AM = 2
1/6/2018 10:40 AM = 1
1/6/2018 10:50 AM = 2
1/6/2018 11:00 AM = 3
1/6/2018 11:40 AM = 1
1/6/2018 11:50 AM = 2
1/6/2018 12:00 PM = 3
1/6/2018 12:10 PM = 4
1/6/2018 12:20 PM = 5
1/6/2018 12:30 PM = 6
1/6/2018 12:40 PM = 7
1/6/2018 12:50 PM = 8

So the CF will produce this list:

1
2
1
2
1
2
3
1
2
3
4
5
6
7
8

Any insights would be appreciated. Thanks!

Don
 

[siroos12]

You would need to sort records based on the time stamp field and run a looping script using the "GetNthRecord" function.

 

Use below calculation to get the time stamp value from previous record :

 

GetNthRecord ( MyTable::TimeStamp ; Get ( RecordNumber ) - 1 )

 

Set above as a variable, then compare it with time stamp value of current record. If the difference is more than the defined break period, then reset the index and set the index field.

Obviously the very first record after sorting based on time stamp would skip the loop as there is no record before it.

 

Hope I was clear enough.

Edited January 8, 2018 by siroos12

[dwdata]

Hi Siroos,

The Timestamp List is not records but a text list in a calculated field, so unless I am missing something, your suggesting won't cover my needs.

I was able to to script the LOGIC I need, nut cannot migrate it to a CF successfully:

 

Set Variable [ $source ; Value: " 1/6/2018 9:00 AM¶ 1/6/2018 9:10 AM¶ 1/6/2018 9:40 AM¶ 1/6/2018 9:50 AM¶ 1/6/2018 10:40 AM¶ 1/6/2018 10:50 AM¶ 1/6/2018 11:00 AM¶ 1/6/2018 11:40 AM¶ 1/6/2018 11:50 AM¶ 1/6/2018 12:00 PM¶ 1/6/2018 12:10 PM¶ 1/6/2018 12:20 PM¶ 1/6/2018 12:30 PM¶ 1/6/2018 …" ] 
Set Variable [ $unit ; Value: 10 ] 
Set Variable [ $counter ; Value: 1 ] 
Set Variable [ $index_counter ; Value: 1 ] 
Set Variable [ $index ; Value: "" ] 
Set Variable [ $max ; Value: ValueCount ( $source ) ] 
Loop
	Exit Loop If [ $counter > $max ] 
	Set Variable [ $index ; Value: List($index; $index_counter) ] 
	If [ GetAsTimestamp (Substitute(MiddleValues ( $source ; $counter + 1 ; 1 ); "¶"; "")) = GetAsTimestamp (Substitute(MiddleValues ( $source ; $counter ; 1 ); "¶"; "")) + Time ( 0 ; $unit ; 0 ) ] 
		Set Variable [ $index_counter ; Value: $index_counter + 1 ] 
	Else
		Set Variable [ $index_counter ; Value: 1 ] 
	End If
	Set Variable [ $counter ; Value: $counter + 1 ] 
End Loop
Show Custom Dialog [ $index ] 

Once again, appreciate any help!

 

Don ;o)







 

Edited January 10, 2018 by dwdata

[siroos12]

Sorry for my misunderstanding. Please try this:

 

Set Variable [ $source; Value:"2018/06/01 9:00 AM" & ¶ &
"2018/06/01 9:10 AM" & ¶ &
"2018/06/01 9:40 AM" & ¶ &
"2018/06/01 9:50 AM" & ¶ &
"2018/06/01 10:40 AM" & ¶ &
"2018/06/01 10:50 AM" & ¶ &
"2018/06/01 11:00 AM" & ¶ &
"2018/06/01 11:40 AM" & ¶ &
"2018/06/01 11:50 AM" & ¶ &
"2018/06/01 12:00 PM" & ¶ &
"2018/06/01 12:10 PM" & ¶ &
"2018/06/01 12:20 PM" & ¶ &
"2018/06/01 12:30 PM" & ¶ &
"2018/06/01 12:40 PM" & ¶ &
"2018/06/01 12:50 PM" ]
Set Variable [ $unit; Value:600
//10 minuets in secounds ]
Set Variable [ $counter; Value:1 ]
Set Variable [ $index_counter; Value:1 ]
Set Variable [ $index; Value:"" ]
Set Variable [ $max; Value:ValueCount ( $source ) ]
Loop
Exit Loop If [ $counter > $max ]
If [ $counter ≠ 1 and GetAsTimestamp ( GetValue($source;$counter) ) - GetAsTimestamp ( GetValue($source;$counter-1)) = $unit]
Set Variable [ $index_counter; Value:$index_counter+1 ]
Else
Set Variable [ $index_counter; Value:1 ]
End If
Set Variable [ $index; Value:GetValue($source;$counter) & "=" & $index_counter ]
Set Variable [ $Results; Value:$Results & ¶ & $index ]
Set Variable [ $counter; Value:$counter+1 ]
End Loop
Show Custom Dialog [ Message: $Results; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

 

 

[dwdata]

17 hours ago, siroos12 said:

Sorry for my misunderstanding. Please try this:

--- snip ---

Yeah - I think we have a communication problem ;o)

My end goal is to have a CUSTOM FUNCTION (recursion) that does the same logic as my working script. I was able to come close:
 

indexList ( theList; unit) = 

If(Get ( WindowMode )  ≠ 0; "";

Let ( [

   $i = $i + 1 ; // initialize counter

  curVal = GetValue ( theList ; $i ) ;
  nextVal = GetValue ( theList ; $i + 1 ) ;
  $index = If(GetAsTimestamp(curVal) + Time(0;unit;0) = GetAsTimestamp(nextVal); $index + 1 ; 1);
  result = $index

  ] ;

  Case ( $i > 1 ; ¶ ) & result &

  Case (

    $i = ValueCount ( theList ) ;

    Let ( $i = "" ; "" ) ; // reset counter

    indexList ( theList; unit)

  )

))

but there are some quirky things going on with it.

1) The FIRST evaluation is showing at the end of the list instaed on the first value. (This is not a deal breaker, it just bugs me. My search will still work as long as the result is STORED - see below)

2) Since the are UNSTORED reference fields (based of relationships), the CF blows up when I go into FIND MODE (Probably due to the Variables). Also, it won't work in a relationship, because it is unstored. GOT TO STORE IT. I will most likely do that on the FLY when needed.

Anyway, I am coming close to a workaround to get me where I need to be. I have been working through this on a test file. Once I figure it out and if it is easy to follow, I might attach it up on this thread for educational purposes. Thanks all!

Don