Key Value Extraction Array
Forums
Well I just learned something tonight. The IDEAScript for a key value extraction uses an array to hold all the values to extract. When I was creating the script I assumed that the array was one of string since it was holding text but when I did that the script kept giving me an error when I got to that line. Turns out that when you define the array you either don't tell IDEA what it is or define it as a Variant so it needs to be one of these:
Dim myArray(0,0) or Dim myArray(0,0) as Varient - if you define it as string it will give you an error. The code per the language browser is correct, they just don't give any explanation for what type the array is.
Sub Main
' Open the database
Set db = Client.OpenDatabase("Sample-Customers.IMD")
' Create the task.
Set task = db.KeyValueExtraction
' Define an array to specify the country names.
Dim myArray(1,0)
myArray(0,0) = "ARGENTINA"
myArray(1,0) = "SOUTH AFRICA"
' Configure the task.
task.IncludeAllFields
task.AddKey "COUNTRY", "A"
task.DBPrefix = "KeyVal"
task.CreateMultipleDatabases = True
task.ValuesToExtract myArray
' Perform the task and output the resulting database name.
task.PerformTask
dbName = task.DBName
' Clear the memory.
Set task = Nothing
Set db = Nothing
' Open the result.
Client.OpenDatabase(dbName)
End Sub
Key Value Extraction larger than 500 items
In IDEA there is a limitation of 500 items for the key value extraction. So if you have more than that you would have to do multiple passes of the key value extraction. The attached script is a simple one that will do it for you. What it does is it first creates a summary file based on the key and then extractions all the items for that key. This way you also get around having to show the dialog. The current limitations is that it will only handle one key and it will extract all the items based on that key, you don't get to pick and choose. Anyone thinking of using the key value extraction can review the attached code. I also attached a spreadsheet of random number from 1 to 2000 so this will create 2000 key value extractions. The script was created using V9 so I don't know if it will work in previous versions as I haven't tested them.
Not sure why, I just tried it
Not sure why, I just tried it using the example file that I supplied and it worked fine. This was on IDEA 8.5. What version of IDEA are you running this on? Also where you using the test file or one of your own files? It probably took around 10 minutes to run on the test file but I don't have a high end computer here at work.
For IDEA V8
If you want to try this in IDEA V8 you might get an error in the summary. If so add the following line and it should work:
Function Summarization
Dim db As database
Dim task As task
Set db = Client.OpenDatabase(sFilename)
Set task = db.Summarization
task.AddFieldToSummarize sField
task.IncludeAllFields
sSummaryFile = client.uniqueFilename(getFileName(sFilename, 1) & "-Sum")
task.OutputDBName = sSummaryFile
task.CreatePercentField = FALSE
task.StatisticsToInclude = SM_COUNT
task.PerformTask
Set task = Nothing
Set db = Nothing
End Function
Ok, so continuing on with
Ok, so continuing on with your response, how can one dynamically build the array of key items to extract? I guess I was a little disappointed that there wasn't a option for IDEAScript to auto-populate the array based on the unique values in the specified key field, much in the same way the dialog box does it for you.
I can sort of get around this by including a blank value for the array and invoking task.DisplaySetupDialog then clearing the "blank" error, then clicking Include All, then OK. But that's 3 clicks I am trying to automate....
Are you trying to do this as
Are you trying to do this as an IDEAScript? If so the key values are basically just a summary of the keys that are selected. If you are doing this in IDEAScript you first need to create a summary and then populate your key array with the items from your summary (maximum 500) or you can create an array and read the file yourself and populate the array that way. Unfortunately there is no auto-populate command in IDEAScript.
I am not sure if I answered your question, let me know if you are talking about something else.
Brian
I have some code (part of a
I have some code (part of a larger script) that attempts to do what folks have described above--auto-populate the array containing the key values to be extracted by summarizing the original database. However, I am getting the error "You must create at least one key value" when it comes to the KeyValueExtraction.PerformTask step. I have confirmed that at least the first element of my array does contain a string value. Any ideas on how to address this?
Option Explicit
'Dim global variables here
Dim sourceDbName As String
Dim summField As String
Dim summFieldArray() As String
Dim db As Object
Dim field As Object
Dim task As Object
Sub Main
sourceDbName = Client.CurrentDatabase.Name 'name of the database to work on
summField = "NAME"
'Create array containing summField values to extract
Call getFieldContents(sourceDbName, summField)
'Use key value extraction to create a database for each value in summField
Call extractDbs(summField, summFieldArray())
End Sub
Function getFieldContents(inputDbName, fieldName As String) 'Get the contents of the specified field as an array
Dim dbName As String
Dim i As Integer
Dim rs As Object
Dim rec As Object
'Summarize input database on specified field to get a database containing the field's unique values
Set db = Client.OpenDatabase(inputDbName)
Set task = db.Summarization
task.AddFieldToSummarize fieldName
dbName = "Temp Summ.IMD"
task.OutputDBName = dbName
task.CreatePercentField = FALSE
task.PerformTask
db.Close
Set task = Nothing
Set db = Nothing
'Extract the feedback values and place them in an array
Set db = Client.OpenDatabase(dbName)
ReDim summFieldArray(db.Count-1)
Set rs = db.RecordSet
rs.ToFirst
For i = 0 To db.Count-1
rs.Next
Set rec = rs.ActiveRecord
summFieldArray(i) = rec.GetCharValue(fieldName)
Next i
Set rs = Nothing
db.Close
Set db = Nothing
Client.DeleteDatabase dbName
End Function
Function extractDbs(keyField As String, keyValues() As Variant)
Set db = Client.OpenDatabase(sourceDbName)
Set task = db.KeyValueExtraction
task.IncludeAllFields
task.AddKey keyField, "A"
task.DBPrefix = "keyVal"
task.CreateMultipleDatabases = TRUE
task.CreateVirtualDatabase = False
MsgBox "VarType = " & VarType(keyValues) & "; IsArray = " & IsArray(keyValues) & "; First value = " & keyValues(0)
task.ValuesToExtract keyValues
task.PerformTask
dbName = task.DBName
Set task = Nothing
Set db = Nothing
End Function
Maximum number in array
I just figured out that the maximum number of items you can have in the array above is 500 or 0 to 499. So when defining the array the largest you could have is
Dim myArray(499,0) - adding 500 instead will give an error.