Flag | subscriber id | Claim number | data 1 |code | data till 900th byte
Input file 2: An FB file 0f 900 bytes .
same layout as above.
Input file 3: An FB file 0f 900 bytes .
This is a code file .
code : length 5, type: charachter , starting position: 1
code1: length 5, type: charachter , starting position: 6 and so on..
so the layout is like :
Code | code1 | code 2| code3..... so on till 900 bytes .
codea| code aa | code b| code c............
codex|...............
it means that code is related to code1
code is realted to code2
code is related to code3 .....and so on...
Problem :
I need to extract from file1 all the rows and form a output file (of ths same format as file1) when :
condtion 1:
there a matching row in file2 having same subscriber id and same data 1 as in file 1 , and the flag in input file2 has 'P' for the selected same subscriber and the value of code in file1 has a entry in file3 such that code in file 1 is realted to code in file 2.
eg:
File 1:
Flag | subscriber id | Claim number | data 1 | code | data till 900thbyte
D abc2374923759 59743579 02902 45656 ................
File 2:
Flag | subscriber id | Claim number | data 1 | code | data till 900th byte
P abc2374923759 99741234 02902 34556 ................
file 3:
Code | code1 | code 2| code3..... so on till 900 bytes .
45656 30556 42842 34556
Now , since
sub id of file1 =file 2 sub id
data1 of file 1 =file 2 data1
file2 has a P for this subid
and file3 has a entry
where
code of file 1 is realted to code in file 2 as per entry in file 3 .
Thus this row will be selected from file 1 and will be populated in output file.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
I suspect there's no practical way to do this with DFSORT/ICETOOL, but if you want me to make sure, I need you to supply some more information:
Is there just one record in each input file or multiple records in each input file?
It looks like the code record in file3 contains about 166 related codex entries. Is that correct?
It looks like you want to match the code in file1 against the first field in file3 and then search all 166 codex entries in that file3 record for the code in file2 to see if there's a match. Is that correct?
Are there multiple records in file1 with the same subscriber id and data 1 value?
Are there multiple records in file2 with the same subscriber id and data 1 value?
You only show 1 record in each file in your example - not much to go on. It would help if you would show an example of the input files with more records and the expected output records, that covers all of the variations including duplicates within file1 and/or duplicates within file2 if they can exist.
Joined: 23 Dec 2005 Posts: 61 Location: Providence , US
Hi Frank,
I am sorry , i thought i covered almost all the requirements but i missed out on many important things.
Quote:
Is there just one record in each input file or multiple records in each input file?
yes ! there are multiple records in the file .
Quote:
It looks like the code record in file3 contains about 166 related codex entries. Is that correct?
nope , my mistake again i missed out on detailed explanation, the code can have atmost 166 realted entries, like it might have only 3 related entries.
thus file 3 can look like this :
code | code1 | code 11 | code 111
codea| codeaa
codeb| codebb | code bbb| code bbc| code bbd
Quote:
It looks like you want to match the code in file1 against the first field in file3 and then search all 166 codex entries in that file3 record for the code in file2 to see if there's a match. Is that correct?
yes ! you are correct , i want to check the same , if there is a related entry then do the same.
Quote:
Are there multiple records in file1 with the same subscriber id and data 1 value?
Are there multiple records in file2 with the same subscriber id and data 1 value?
yes ! there are multiple records with same subscriber and data1 value , but we dont have any duplicates as claim number is unique. for any row.
Quote:
You only show 1 record in each file in your example - not much to go on. It would help if you would show an example of the input files with more records and the expected output records, that covers all of the variations including duplicates within file1 and/or duplicates within file2 if they can exist.
only good part is there are no duplicates within the file ,
new example:
File 1:
Flag | subscriber id | Claim number | data 1 | code | data till 900thbyte
D abc2374923759 5974357911 02902 45656 ................
D xyz123456789 1234566711 92902 V5670 ................
D abc2374923759 3456781211 02902 23456 ................
File 2:
Flag | subscriber id | Claim number | data 1 | code | data till 900th byte
P abc2374923759 99741234 02902 34556 ................
D xyz6492349792 25723959 78902 42342 ...................
P abc2374923759 3456781211 02902 V2345
in file 3 once we start having spaces there are no further related codes present for code in first column.
Output file:
Flag | subscriber id | Claim number | data 1 | code | data till 900th byte
D abc2374923759 59743579 02902 45656 ................
D abc2374923759 3456781211 02902 23456 ................
Please do let me know , if there any questions about my requirement.
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
Sorry, but as I suspected, I don't see a practical way to do this with DFSORT due to the possibility of up to 166 code entries in each file3 record. That would require a dynamic lookup and change capability.
