I have a requirement to Join two files and concatenate some fields from second file and append to the end of the first file.
* Highlighted the Join Key field in Green for two input files FILE1 & FILE2
* Highlighted the field in brown in FILE2 that has to be concatenated at the end of the FILE1
* Highlighted the New fields required in OUTPUT file in Orange.
FILE1 LRECL=100, KEY POSITION (11,4)
FILE2 LRECL=200,KEY POSITION (10,4)
A|1000499|8453 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|5534 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|1086 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|1119 |20863|734|||143|2 HEALTH AND BEAUTY AIDS|
A|1000499|8453 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|60
A|1000499|5534 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|60
A|1000499|1086 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|2
A|1000499|1119 |20863|734|||143|2 HEALTH AND BEAUTY AIDS |ANA|Analytic|23
Are there always three records with each key in input file2 as shown in your example? Are the three records always in the order shown or is there some other way of identifying which type of record it is? What is the position and length of the data you want from each of these three records?
Each record in file2 with a particular key seems to have a different structure, e.g. 8453 record with ANA, 8453 record with Analytic, 8453 record with 60. How can we identify each type of record and where it's data is. For example, how can we identify the record with ANA and what is the position and length of ANA? How can we identify the record with Analytic and what is the position and length of Analytic? How can we identify the record 60 and what is the position and length of 60?
In the future, please just use code tags instead of using colors. With code tags, we can tell the positions of the fields. With colors, we can't.
To elaborate my requirement . There may be as many records with each key in file2 ( Just showed you an example pattern of how the file will look).
File2 will be a VB file and I want to parse only the last field of the file2.
Suppose I have 7 fields in file2. I want to ignore first six fields and I am interested in the last field only and will append to FILE1. The Join key for FILE1 AND FILE2 will be (11,4) AND (10,4)
I cannot tell exactly where the last field will start. It may be 80,81 ,90 ,91 and so on. Data type of last field can be numeric or Alphanumeric and it will again be a Variable length field.
This file generated outside in frontend so I will not have any clues on the length of each record.This is the requirement I got and I am also very confused on how to handle this.
1. Your file2 VB file data is '|' delimited and last field have a max field length of 30.
2. There are can be a max of 9 dups for each key in the VB file.(You can expand this upto 99 if you want by adding more IFTHEN statements and adjusting the field positions)