Key Value Extraction Array

10 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