Find the group that exceeds...
Forums
Hello, I have a question regarding IDEAScripting. This is something that I can easily do on SQL but I haven't managed to find a solution for this using IDEA.
Given the sample data on the attached image, I am looking to find a group of transactions, within 5 days from eachother, which sum of amount is above 5000.
For the given data it would be the transactions on the following days:
- 06/03/2019 and 07/03/2019 totalling 5300;
- 04/03/2019, 06/03/2019 and 07/03/2019 totalling 5800.
Ultimately, I'll solve this taking advantage of the IDEA's Python integration.
Thanks in advance.
Interesting problem. I have
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
Hi myrahz,
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