Page 1 of 1

How to sort 2 VB files with Key

PostPosted: Mon Jul 20, 2015 6:00 pm
by Mahesh_1223
Hi,

I have 2 VB input files.

File1:

RAJESH,M,111,AAAA
RAMS,M,22,BBB
SURESH,M,3333,CCCC

File2:
Employeedetails,111,10,20,30
Employee,22,100,20,3
Employeeposition,3333,1,60,300

The above are 2 input files. I need some data from file1 and some data from file2. Key is 111, 22 and 3333 are these are common in both the files. As these files are Comma delimited we don't know exactly the length of each field, so we cannot use FIXLENGTH while Parsing.

Output file:
Employeedetails,111,Rajesh,10,20,30
Employee,22,RAMS,100,20,3
Employeeposition,3333,SURESH,1,60,300

Could you please let me know how can we do this using sort. Lets say the records are in sorted order.

Re: How to sort 2 VB files with Key

PostPosted: Mon Jul 20, 2015 7:29 pm
by BillyBoyo
FIXLEN tells SORT how long the extracted field is, not how long the input field is.

You have a simple JOINKEYS, with a need to extend your records in JFN1CNTL and JNF2CNTL to have them in a fixed position (extracted with PARSE).

REFORMAT statement to format the data you want.

Lots of example here.

Re: How to sort 2 VB files with Key

PostPosted: Mon Jul 20, 2015 7:39 pm
by Mahesh_1223
Thanks Billy,

I tried a lot but could not find an example on this or related to this. Could you please help me the location to find the same.

Re: How to sort 2 VB files with Key

PostPosted: Tue Jul 21, 2015 12:51 am
by Mahesh_1223
Here I have 2 input files. I can use JOINKEYS if I know exact position, as it is variable length file I don't know exactly the position of the key to compare.

Could you please let me know how to compare the 2 VB files with key.

Re: How to sort 2 VB files with Key

PostPosted: Tue Jul 21, 2015 12:05 pm
by BillyBoyo
Yes, JOINKEYS requires a fixed key. So you have to make one (two).

//JNF1CNTL allows you to pre-process file one of a JOINKEYS. //JNF2CNTL allows you to...

You need to extend your records temporarily in the JNFnCNTLs.

For VB, you extend at the beginning of the record.

  INREC  BUILD=(1,4,10X,5)


That would create 10 blanks, as an example.

Yours will be something like this:

  INREC PARSE=(...), to get your key into a named, fixed-length, PARSE field
        BUILD=(1,4,%nn,5)


Put that in both JNFnCNTLs, user 5,nn for the length and position of the key on both JOINKEYS,

You can (probably/possibly) do all the formatting you need with the REFORMAT statement

  REFORMAT FIELDS=(F1:1,4,x,y,F2:a,b:F1,z)

Re: How to sort 2 VB files with Key

PostPosted: Tue Jul 21, 2015 1:23 pm
by Mahesh_1223
Hi Billy,

I coded the Sort based on your yesterday suggestions and It worked. Thanks.

Re: How to sort 2 VB files with Key

PostPosted: Tue Jul 21, 2015 1:53 pm
by BillyBoyo
Good work. Can you post the code please, so that we have a nice fresh example which can help others with a similar task?