Combine multiple matching records into one creating a few array fields

Looking for the best way to combine records using IPA v11 with LSF v10 –on premise

I have an Inbound delimited file with 65 fields. I only use around 20 fields. The records are in no particular order, usually less than 50 records.

I need to combine the records that have the same number in field-11 (F-11). There are only 4 fields that have different data if F-11 matches.

My preference is to create array fields, for the 4 with different data and combine the matching records into one with the different values in the arrays. What is the best way to do this?

Will the Data Transformation node do this? I’ve never used the ‘WebSphere Transformation Extender Design Studio’. According to the documentation it comes with IPA, but I don’t think we even have it installed for me to look at.
We’re on premise so I can use a System Command node and run a script to transform the data. We’ve used SysCmd node for simple bcp stuff so familiar with setting up the node, but honestly I don’t do a lot of scripting. Is this something PowerShell can do? Is there a different/better script language to use?
Other suggestions?

I need someone to point me in the right direction. If the Data Transformation node will do what I need then I’ll figure out how to design the map. I just don’t want to waste a lot of time looking at it if it’s not designed to do what I need. Same with the script, I think I can do what I want with PS, but I don’t know the PS commands so can you give me the basic commands I would need and I’ll figure it out the rest.

Thanks in advance!

Joan

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

All comments

Legacy Contributor

If your flow has permission to create a table, you can load the records from the file into the table, have your way with the data using sql, and write them back out to another file if needed.

Legacy Contributor

That's a good thought. Not sure how our DBA will feel about it, but I'll ask.

trigger the flow, read file, use sql node to create temp table and populate, then sql again to pull my data and process.

jreese

When you say that you need to combine records what exactly do you mean. Are the 4 fields that are different values that you need to add together?

The DataIterator node will allow you to read in each record and be able to get the values of each field do any combining using a File Access node and if you only need 20 fields for the output write them out to a new file.

Legacy Contributor

Here is an example;

records 2, 3, & 8 are all for mr. Smith (F-11 matches). The name, address, company, etc.. are all the same in records 2,3, & 8; but Desc, Amt, Seq, Ref have different values in each record. In the end I need to create a lawson transaction ONCE for Mr.Smith with multiple distribution lines. but there is no order to the file.

kyle-tucker

Depending on where the file is you could run a script via syscmd to sort the file. We did something similar with a payroll file by process level, department, employee. We were on Unix and put the file there and ran a sort on it prior to interpreting the data.

Legacy Contributor

A decent sort might help you. The one that comes with cygwin gives you some pretty good options. You need to find where your cygwin utilities are stored and point to them in the syscommand node, otherwise you just end up using the windows sort, which doesn't do much. Our cygwin exe's are located in D:cywinin (where is the lawson app server). There are some good examples of all the options it has on line. Here is one: https://shapeshed.com/unix-sort/

Here is a sample syscmd that sorts a pipe delimited file by field 2 then field 2, and redirects the output to another file:

d:cygwininsort.exe -t"|" -k2n -k1 d:mainframeftpfilesflowworkFileIn.csv > d:mainframeftpfilesflowworkFileSorted.csv

jreese

I would agree that since you are on-premise, and can still use the syscmd node, I would sort the records by field 11 and then you should be able to read through the file with the DataIterator and create the header record and the different distribution records.

Legacy Contributor

A sort is something I considered. I figured that if I need a syscmd to run a script anyway, I could probably make the script do more than just sort the data. To make the rest of the IPA flow easier

kyle-tucker

I saw you mentioned using PS to sort. If the file type is .csv PS has a built in parse and sort for that. (i.e. Import-Csv .email-subscribers.csv | sort <field>).

If it's a different file type it's still possible (Get-Content .maildat.txt | foreach {} | sort {}), but you would need to do a little more work here to parse each line and what field/value/fixed space in the line should be sorted.

Copyright © 2025 Infor. All rights reserved.