Key Value Extraction Array

16 posts / 0 new
Last post
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57
Key Value Extraction Array

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


Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.

wsn4iv
Offline
Joined: 06/21/2016 - 16:02

I tried using the script you provided, but the database extractions are empty. Any idea why?

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

E.Heizyk
Offline
Joined: 11/29/2018 - 16:53

Do you know if there is any way to do a key value extract without defining each key value that you want? I want a consistant field to always be used for the extraction but its contents may be different for different files.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

You need to know what the contents are in order to extract the items.  So you would have to do a summary before hand to obtain the items that you want to extract.  I am not sure if I am answering your question properly, maybe you could give an example.

tstraub
Offline
Joined: 08/16/2018 - 16:44

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....

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

rachel.organist
Offline
Joined: 12/12/2019 - 12:21

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

Pages