How to sort 2 VB files with Key



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

How to sort 2 VB files with Key

Postby Mahesh_1223 » Mon Jul 20, 2015 6:00 pm

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.
Mahesh_1223
 
Posts: 4
Joined: Thu Jul 16, 2015 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to sort 2 VB files with Key

Postby BillyBoyo » Mon Jul 20, 2015 7:29 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to sort 2 VB files with Key

Postby Mahesh_1223 » Mon Jul 20, 2015 7:39 pm

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.
Mahesh_1223
 
Posts: 4
Joined: Thu Jul 16, 2015 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to sort 2 VB files with Key

Postby Mahesh_1223 » Tue Jul 21, 2015 12:51 am

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.
Mahesh_1223
 
Posts: 4
Joined: Thu Jul 16, 2015 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to sort 2 VB files with Key

Postby BillyBoyo » Tue Jul 21, 2015 12:05 pm

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)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: How to sort 2 VB files with Key

Postby Mahesh_1223 » Tue Jul 21, 2015 1:23 pm

Hi Billy,

I coded the Sort based on your yesterday suggestions and It worked. Thanks.
Mahesh_1223
 
Posts: 4
Joined: Thu Jul 16, 2015 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to sort 2 VB files with Key

Postby BillyBoyo » Tue Jul 21, 2015 1:53 pm

Good work. Can you post the code please, so that we have a nice fresh example which can help others with a similar task?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post