is there a way or an example) of doing the following:
I have 2 text files with 3 columns each - the date, a store number, and a count
I need to combine these 2 files into a single (third) text file, but I cannot have duplicate store number records in the result file.
My first thought is to load all data from one file into the new file, and then load data from the second file but ignore any duplicate store records. So basically, the third file should have the store number as the "key".
Also, if I can, the transaction being discarded (the duplicate) needs to be written to an error file.
Any quick thoughts or hints? would it be easier to have the third file as an .xlsx file?
There are a couple of options here ...
Since you're connected to an iSeries, you should be able to take advantage of QTemp and SQL to get this done. The following pseudo code should give you what you're looking for.
Read File1 (rowset.File1) Read File2 (rowset.File2) SQL - Create Table QTemp.File1 as (c1, c2, c3) Create Table QTemp.File2 as (c1, c2, c3) Create Table QTemp.File3 as (c1, c2, c3) Create Table QTemp.Errors as (c1, c2, c3) Insert rowset.File1 into QTemp.File1 Insert rowset.File2 into QTemp.File2 Insert into QTemp.File3 (Select * from QTemp.File1) Insert into QTemp.Errors (Select * from QTemo.File2 where "key" exists in QTemp.File1) Insert into QTemp.File3 (Select * from QTemp.File2 where "key" does not exist in QTemp.File1) Select * from QTemp.File3 (Final File) Create CSV/Txt/Excel, etc...
Lead Solutions Consultant