First of all apologies if I do some newbie questions.
I have the following files to do an unpaired join:
FILE 1
0HDR UK1122220100131
10001AAAA010 XXXXXXXXXXXXXXX
10002BBBB020 XXXXXXXXXXXXXXX
10003BBBB010 XXXXXXXXXXXXXXX
10004CCCC030 XXXXXXXXXXXXXXX
10005DDDD020 XXXXXXXXXXXXXXX
10006EEEE010 XXXXXXXXXXXXXXX
10008EEEE010 XXXXXXXXXXXXXXX
9TRL UK112222010013000090110
10001AAAA010 XXXXXXXXXXXXXXX
10002BBBB020 XXXXXXXXXXXXXXX
10003BBBB010 XXXXXXXXXXXXXXX
10004CCCC030 XXXXXXXXXXXXXXX
10005DDDD020 XXXXXXXXXXXXXXX
10006EEEE010 XXXXXXXXXXXXXXX
10008EEEE010 XXXXXXXXXXXXXXX
9TRL UK112222010013000090110
FILE 2
0HDR UK1122220100130
10000AAAA020 XXXXXXXXXXXXXXX
10001AAAA010 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10004CCCC030 XXXXXXXXXXXXXXX
10005DDDD020 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK112222010013100100160
10000AAAA020 XXXXXXXXXXXXXXX
10001AAAA010 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10004CCCC030 XXXXXXXXXXXXXXX
10005DDDD020 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK112222010013100100160
And I need the following output:
0HDR UK1122220100130
10000AAAA020 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK112222010013100070100
10000AAAA020 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK112222010013100070100
If you can see I need the records from file 2 that are different from file one. I am able to get the records with the unpaired join but my issue is with the trailer. I am stuck updating it. Here it is my piece of code (draft one)
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F2:1,28,F1:1,28)
SORT FIELDS=COPY
OUTFIL INCLUDE=(2,3,CH,EQ,C'HDR',OR,
6,7,CH,NE,34,7,CH,OR,
2,3,CH,EQ,C'TRL'),
IFTHEN=(WHEN=(2,3,CH,EQ,C'TRL'),
BUILD(1,20,21:C'XXXXXXXX')),
IFTHEN=(WHEN=NONE,BUILD(1,28)),REMOVECC,
TRAILER1=(1:C'TRR',4:COUNT=(LENGTH=3),
8:TOT=(10,3,ZD,EDIT=(TTTT)))
JOINKEYS FILES=F2,FIELDS=(1,5,A)
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F2:1,28,F1:1,28)
SORT FIELDS=COPY
OUTFIL INCLUDE=(2,3,CH,EQ,C'HDR',OR,
6,7,CH,NE,34,7,CH,OR,
2,3,CH,EQ,C'TRL'),
IFTHEN=(WHEN=(2,3,CH,EQ,C'TRL'),
BUILD(1,20,21:C'XXXXXXXX')),
IFTHEN=(WHEN=NONE,BUILD(1,28)),REMOVECC,
TRAILER1=(1:C'TRR',4:COUNT=(LENGTH=3),
8:TOT=(10,3,ZD,EDIT=(TTTT)))
Please, note that the TRAILER1 record shouldn't be there. It is there just to double check. My issue is that I don't know how to put in the XXXXXXXX' the record count and the amount total of the detailed records. My output is as follows:
0HDR UK1122220100130
10000AAAA020 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK1122220100131[b]XXXXXXXX[/b]
TRR 7 0544
10000AAAA020 XXXXXXXXXXXXXXX
10002AAAA010 XXXXXXXXXXXXXXX
10003BBBB040 XXXXXXXXXXXXXXX
10006EEEE020 XXXXXXXXXXXXXXX
10007AAAA010 XXXXXXXXXXXXXXX
9TRL UK1122220100131[b]XXXXXXXX[/b]
TRR 7 0544
I need in the bold X group the record count (lenght 4) and the amount of fields (10,3) (length 4).
In addition if someone could tell me how to avoid the adding of TRL and HDR in the TRAILER1 sentence to the totals I also be grateful.
Thanks very much in advance,
Regards,
Enrique.
