Summarization Script
Forums
Hello,I want to do a summarization with this script. I have a problem with the code, line 107 "Need to specify a field to summarize".
Someone can help me to resolve this script ?
Thank you
Hi ValeIT,
Hi ValeIT,
What you need to do from the summary function is removing the include all fields line and add individual lines for each field you want as part of the summary, such as:
task.AddFieldToSummarize "FIELD1"
task.AddFieldToSummarize "FIELD2"
and add any fields you want to total on:
task.AddFieldToTotal "NUMBER_FIELD_1"
I think that should get the function working.
Brian
Hello Bryan,
Hello Bryan,
Thank you but it doesn't work or perhaps I don't really understand your comment. So, I explain my problem
When I write that, it works with the sample files
Set db = Client.OpenDatabase("Sample-Customers.IMD")
Set task = db.Summarization
task.AddFieldToSummarize "CREDIT_LIM"
dbName = "Summarization.IMD"
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
but when I write with an another database, it doesn't work :
Set db = Client.OpenDatabase(filename)
Set task = db.Summarization
task.IncludeAllFields
dbName = client.UniqueFilename(newFilename)
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
I enclose my script if you have time to check this.
Thank you
The problem is that with a
The problem is that with a summarization you need to list the fields that you want summarized. In your first part you are summarizing the CREDIT_LIM field but in the second part you have the task.IncludeAllFields that doesn't work here. You need to specifiy the fields from the filename variable that you want to summarize by adding them with the task.AddFieldToSummarize function, same as you did in the first part. The task.AddFieldToSummarize could have a variable to hold the fieldname. Here is an example from my Bendord 1st digit test, you can see I am using the task.AddFieldToSummarize with a variable that relates to the file from the step1FileName variable, so I have a variable that holds the filename and then variable(s) to hold the fields related to that file that I want to summarize:
'****************************************************************************************************
' Name: step3
' Description: Routine to summarize based on the client field
' Accepts: Nothing
' Returns: Nothing
'****************************************************************************************************
Function step3() 'summarize based on the client field
Dim db As database
Dim task As task
Dim dbName As String
Dim i As Integer
Set db = Client.OpenDatabase(step1FileName)
Set task = db.Summarization
task.AddFieldToSummarize clientField
task.AddFieldToSummarize first_digit_field
If Not IsVarArrayEmpty(tempListbox2) Then
For i = 1 To UBound(tempListbox2)
If tempListbox2(i) <> clientField And tempListbox2(i) <> amountField Then
task.AddFieldToInc tempListbox2(i)
End If
Next i
End If
dbName = Client.UniqueFileName("Step 3")
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.StatisticsToInclude = SM_COUNT
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function
What you need to do is either
What you need to do is either have the user select the fields to summarize on or find some way of identify those fields in each file. If the columns are always in the same order then you can use that to find the field names. Usually in my scripts I have the script do some tagging so I know which fields I am looking for for that file and then know which fields I want to summarize.
For me when I create a script
For me when I create a script I know what result I want, then I figure out what files / fields I will need to get that result, create an interface to extract that information from the user and then create the functions to get from the selected file(s) to the final result. It can be a bit of a puzzle how to put everything together.
Option ExplicitDim filename
I was inspired by the code available in the video section, thank