Skip to main content

Summarization Script

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
 

Brian Element Mon, 06/11/2018 - 08:49

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

ValeIT Mon, 06/11/2018 - 09:10

In reply to by Brian Element

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

Brian Element Mon, 06/11/2018 - 09:36

In reply to by ValeIT

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

ValeIT Mon, 06/11/2018 - 10:03

In reply to by Brian Element

Ok, thanks but I want to automate this code. If I write on mycode what you propose, it will not work on another database with different column names.Is there a solution to automate this or I have to modify the code for each database?

Brian Element Mon, 06/11/2018 - 10:12

In reply to by ValeIT

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.

Brian Element Mon, 06/11/2018 - 10:42

In reply to by ValeIT

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.