Find the group that exceeds...

Hi myrahz,

For these types of questions you need to use the scripting function of IDEA, I would do this in IDEAScript but if you are doing it in Python it would be great if you could share your code with us so we could learn from it.

Thanks

Brian

Interesting problem. I have put together a quick and dirty code rather than a structured one. Please try this by setting your input db name and output db name (default "result" - this will be created). Limit is defined as a variable and set to 5000 and Gap is defined as a varaible and set to 4 (for a span of 5 days, the gap is 4) so that you could change it later, if required. I added the follwing row to your data just to test the boundary condition - it makes the preceeding 2 rows exceed 5000, but, by itself is not a result candidate, if I understand your requirement correctly.
20190320,3,A,3800
 Regards,
Ravi
 
The code:
 
Sub Main
 
 Dim j_predate,j_xdate As Double
 
 IgnoreWarning(True)
 
 inputfile="data"
 outputfile="result"
 limit=5000
 gap=4 ' for a coverage of 5 days, the gap is 4
 
 ' create outputfile
  Set newtab = Client.NewTableDef
  Set daysfield = newtab.NewField
  daysfield.Name="DAYS"
  daysfield.Type =  WI_CHAR_FIELD
  daysfield.Length = 100
  newtab.AppendField daysfield
  Set amountfield = newtab.NewField
  amountfield.Name="AMOUNT"
  amountfield.Type= WI_NUM_FIELD
  amountfield.Length=10
  newtab.AppendField amountfield
  newtab.Protect = False
  Set newdb = Client.NewDatabase(outputfile, "", newtab)
  Set newtab=newdb.TableDef
  Set newrs = newdb.RecordSet
 
' process the inputfile  
 Set db=Client.OpenDatabase(inputfile)
 Set tab=db.TableDef
 Set rs=db.RecordSet
 recs=rs.Count
 For i=1 To recs
  rs.getAt(i)
  Set rec=rs.ActiveRecord
  predate=rec.getDateValue("DAY")
  xdate=predate
  j_predate=iDtoj(predate)
  j_xdate=j_predate
  tot=0
  xdays="_"
  x=i
  ' accumuldate total till the following rows are within 5 days
  While j_xdate<=(j_predate+gap) And x<=recs
   amt=rec.getNumValue("AMOUNT")
   tot=tot+amt
   If xdays="_" Then
    xdays=Right(xdate,2) & "/" & Mid(xdate,5,2) & "/" & Left(xdate,4) & " (" & amt & ")"
   Else
   xdays=xdays & ", " & Right(xdate,2) & "/" & Mid(xdate,5,2) & "/" & Left(xdate,4) & " (" & amt & ")"
   End If
   ' everytime total exceeds limit, add one instance in the result
   If tot>limit Then
    Set newrec=newrs.NewRecord
    newrec.SetCharValue "DAYS", xdays
    newrec.setNumValue "AMOUNT", tot
    newrs.AppendRecord newrec   
   End If
   If x<recs Then
    rs.Next
    Set rec=rs.ActiveRecord
    xdate=rec.getDateValue("DAY")
    j_xdate=iDtoj(xdate)
   End If
   x=x+1
  Wend
 Next 
 newtab.Protect=True
 newdb.CommitDatabase
 Client.CloseDatabase(inputfile)
 Client.CloseDatabase(outputfile)
 Set rs=Nothing
 Set tab=Nothing
 Set newrs=Nothing
 Set newtab=Nothing
 Set db=Nothing
 Set newdb=Nothing
 
 Client.RefreshFileExplorer
  End Sub