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.

Processing the contents of a Group by Options · View
PaulG
Posted: Friday, July 16, 2021 7:02:57 PM
Rank: Newbie

Joined: 7/16/2021
Posts: 1
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

File Attachment(s):
HD Ticket Input.txt (1kb) downloaded 27 time(s).
HD Ticket.mfd (8kb) downloaded 31 time(s).

PaulG attached the following image(s):
HD Mapping.PNG

K101
Posted: Tuesday, August 3, 2021 9:31:52 AM
Rank: Advanced Member

Joined: 2/27/2009
Posts: 441
Based on the data you supplied, I see no reason you can't use the UserNumber field in conjunction with chaining the text file component via a filter to the Fake SQL.

Output:
Quote:
Update Blah Set Processed = 'Y' Where ID = 1
Update Blah Set Processed = 'Y' Where ID = 2
Update Blah Set Processed = 'Y' Where ID = 3
Update Blah Set Processed = 'Y' Where ID = 4
Update Blah Set Processed = 'Y' Where ID = 5

File Attachment(s):
HD Ticket.2021-08-03.mfd (9kb) downloaded 26 time(s).

K101 attached the following image(s):
example.png

Users browsing this topic
guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

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