IMPORTANT:
this is not a Support Forum! Experienced users might answer from time to time questions posted here. If you need a professional and reliable answer, or if you want to report a bug, please contact Altova Support instead.

Profile: PaulG
About
User Name: PaulG
Forum Rank: Newbie
Real Name:
Location
Occupation:
Interests:
Gender: None Specified
Statistics
Joined: Friday, July 16, 2021
Last Visit: Friday, July 16, 2021 7:30:07 PM
Number of Posts: 1
[0.01% of all post / 0.02 posts per day]
Avatar
Last 10 Posts
Topic: Processing the contents of a Group by
Posted: Friday, July 16, 2021 7:02:57 PM
All,

I'm processing an export that has two targets.
1. An export file
2. Updating the source table and flagging the record as sent.

The major complication is that information is spread across several records and all the records have to be present in order for the data to be exported. The method of doing this (this is pre-existing and cannot be changed) was to add a "NumberRecords" field, which contains the # of records that complete the information. Once that # of records is present, the data can be exported. The only way I could figure out how to perform that check was to use the Group by function. That works, so I'm good there.

Flagging those records as Processed is where I'm being tripped up. The records are coming to me in groups, and I can't figure out how to separate the groups back into records, or at least process the individual ID field values, to update the database. I only want to update the DB if the records are exported, so I have to be able to use the results of the Equals function in the mapping. Since the ID isn't exported, I can't chain the export, at least not as far as I can tell.

I have an example hopefully attached. The Input file contains records that mirror the above example:
ID,UserNumber,NumRecords,Value,Processed
1,A1234,2,AAAA,N
2,B2345,1,BBBB,N
3,C3456,1,CCCC,N
4,D4567,1,DDDD,N
5,A1234,2,AAAA,N

In that file, records 1 and 5 is the example of data being spread across two records, 2-4 are self-contained. If records 1 or 5 are deleted, notice in the Target Output file how the other record is not exported. If both are present, the data appears. Only UserNumber and Value are exported, so I'm able to use the groupings just fine there.

In the "Fake SQL" target, I'm simulating the commands being generated by a target DB component. That's a little misleading because the flexibility shown by generating the command (for example, using "IN" instead of "=" for the where clause) doesn't exist. No command appears for records 1 and 5 if either are deleted from the Input file, which is correct. The problem is that target's output is missing the command to update record 1 when all 5 records are in the Input file.

I get the following warning in the GUI's messages area, so the problem is apparent - I just can't figure how to work around it.
The previous value 'Update Blah Set Processed = 'Y' Where ID = 1' of field 'Command' was overwritten with 'Update Blah Set Processed = 'Y' Where ID = 5'.

The end result of this export should be
1. 4 records in the "Grouped Output" file
2. 5 update statements in the Fake SQL Output
3. if Record 1 or 5 is deleted from the Input file, the two targets should have 3 records each.

Any help would be greatly appreciated. If someone has a better way of doing this, that'd work as well.

Paul

Use of the Altova User Forum(s) is governed by the Altova Terms of Use.