Count occurrences in field

OL Connect Enterprise DataMapper
1

Hi there!

I have a .txt data file with a field named Country at a fixed position. I try to get a report of all diverent occurrences within this field and the amount of occurrences per diverent "“Country”.

I loaded all the lines in the data file and placed all the country fields in an array (in Dutch):

[“Nederland”, “Nederland”, “Nederland”, “Nederland”, “Duitsland”, “Nederland”, “Nederland”, “Nederland”, “Belgie”, “Nederland”, “Canada”, “Nederland”, “Spanje”, “Nederland”, “Duitsland”, “Nederland”, “Nederland”, “Frankrijk”, “Nederland”]

Can anybody help me to find a way to convert this to the needed report looking something like this:

Nederland: 13
Duitsland: 2
Belgie: 1
Canada: 1
Spanje: 1
Frankrijk: 1

Thanks in advance!

2

Hi Filemon,

I do this all the time using a script in Workflow. It is VBScript though and used for calculating postal codes. I modified it quick to accommodate your array and the output is written to a file as follows.

Nederland 13
Duitsland 2
Belgie 1
Canada 1
Spanje 1
Frankrijk 1

Option Explicit
Dim codesIn, codesOut, i, fullFile

Const ForReading = 1
Const ForAppending = 8

Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
Dim indexOut : Set indexOut = fso.OpenTextFile("D:\out.txt", ForAppending, True)

fullFile = Array("Nederland", "Nederland", "Nederland", "Nederland", "Duitsland", "Nederland", "Nederland", "Nederland", "Belgie", "Nederland", "Canada", "Nederland", "Spanje", "Nederland", "Duitsland", "Nederland", "Nederland", "Frankrijk", "Nederland")

ReturnArrayDupeCount fullFile,1

indexOut.Write(codesOut)
indexOut.Close
watch.log codesOut,2

Sub ReturnArrayDupeCount(aTemp, iShow)
  Dim itemNameKey
  Dim d : Set d = GetDupDict(aTemp)

  For Each itemNameKey In d.Keys
    codesOut = codesOut & itemNameKey & " " & d(itemNameKey) & vbcrlf
  Next
  Set d = Nothing
End Sub

Function GetDupDict(aTemp)
  Dim dItems, j
  Set dItems = CreateObject("Scripting.Dictionary")

  For j = 0 To UBound(aTemp)
     If Not IsEmpty(aTemp(j)) and Not aTemp(j) = " " and Not aTemp(j) = "" Then
        dItems(aTemp(j)) = dItems(aTemp(j)) + 1
     End If
  Next
  Set GetDupDict = dItems
  Set dItems = Nothing
End Function

Hope this helps.

Regards,
S

1 Like
3

Cool! Thanks, I’m going to try it out. Thanks for the quick reply

4

Can I replace they array with a record field in the workflow?

fullFile = Array(“Nederland”, “Nederland”, “Nederland”, “Nederland”, “Duitsland”, “Nederland”, “Nederland”, “Nederland”, “Belgie”, “Nederland”, “Canada”, “Nederland”, “Spanje”, “Nederland”, “Duitsland”, “Nederland”, “Nederland”, “Frankrijk”, “Nederland”)

fullFile = CountryFields;

5

I thought you had that part done? You will need to load the data file via the script and load the countries into an array as you mentioned. In my case this script is part of a few scripts all of which consist of ±400 lines of code and in a previous script it wrote the values to a file and this script read that file and did the counts for each unique value.

I just spotted your post while I was on break and thought I would share my script to get you started. Unfortunately I’m a bit busy at work now. Perhaps someone here can help out and if I find the time I will try too.

What format is your data in?

Regards,
S

6

Thanks I figured it out, thanks for your help! :smile:

7

Glad you managed to get it sorted!! It is always a pleasure to help out.

Regards,
S