Need help on merging two records from same file



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Need help on merging two records from same file

Postby Dhiren » Wed Sep 19, 2012 11:01 pm

I had accidentaly posted this query in DFSORT forum.
Re-posting in Syncsort

Hi,
I have multiple records for a given account id and trying to combine all records related to each
account into one single record. Number of records for each account may vary from 1 - 4. Can
someone please help to do this using syncsort?.

Input:
Fixed 19 record length file. No header & trailer record in the file.
3 - input fields with their record position

code Flag1 Flag2 Amount
(1-4) (5) (6)        (7-13)

1210  2  1           000079751.79
1210  1  4           000440494.52
1210  2  4          -000241189.45
1213  2  1           000154520.73
1213  2  4          -000227258.71


Expected output:
6 - output fields

Code# Flag 1  Amount      Amount       Amount       Amount
(1-4)  (5-5)  (6-18)      (19-31)      (32-44)      (45-57)

1210  1         0            0           0          000440494.52
1210  2         0         000079751.79   0         -000241189.45
1213  2         0         000154520.73   0         -000227258.71

Any help will be greatly appreciated.
Thank you.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need help on merging two records from same file

 

Re: Need help on merging two records from same file

Postby NicC » Wed Sep 19, 2012 11:11 pm

So, in your other post the data was coded up so that some semblance of normality was achieved with your code/input/output. Why could you not do it yourself? Use the full editor, select the data to be coded and click the Code button. Click preview to see how it looks and adjust as required. Then we can see what you are trying to do. Edit: I have spent a considerable amount of time lining things up - hopefully correctly. (Hmmm, maybe I should charge for that!)

Now I see the first amount is to go in the amount 4 column - why? What are the rules for getting from input to output?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Need help on merging two records from same file

Postby Dhiren » Thu Sep 20, 2012 12:06 am

Hi Nic,

I had coded the data myself in the first post.
The value in the amount column is based on the Flag2. If Flag2 has a value of 1 then the amount value is written in Amount (1) field and if the Flag2 value is 4 then amount value gets written to Amount (4) column.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need help on merging two records from same file

Postby Akatsukami » Thu Sep 20, 2012 12:17 am

Dhiren wrote:The value in the amount column is based on the Flag2. If Flag2 has a value of 1 then the amount value is written in Amount (1) field and if the Flag2 value is 4 then amount value gets written to Amount (4) column.

It would seem, looking at the input and output you provide, that this statement is wrong, and that the actual logic is
if (Flag2='4')
  then Amount4 = Amount
  else if (Flag1='2')
    then Amount2 = Amount
    else ?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Need help on merging two records from same file

Postby BillyBoyo » Thu Sep 20, 2012 12:20 am

Dhiren wrote:Hi Nic,

I had coded the data myself in the first post.
The value in the amount column is based on the Flag2. If Flag2 has a value of 1 then the amount value is written in Amount (1) field and if the Flag2 value is 4 then amount value gets written to Amount (4) column.


You probably don't understand about the "Code".

Here is an example of what happens with the Code button.

Here    is an    example   of what      happens with the Code   button.


The last two lines of text are identical. In the second, the spacing is "preserved". Which makes it a good way to "mark-up" code and data so that they line-up character-for-character in columns, just like your mainframe screen.

I Code'd your first post.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Need help on merging two records from same file

Postby Dhiren » Thu Sep 20, 2012 12:31 am

Hopefully this makes sense.
I have corrected my Output

Code# Flag1   Amount1        Amount2       Amount3     Amount4
(1-4)  (5-5)  (6-18)         (19-31)      (32-44)      (45-57)

1210  1         0            0             0            000440494.52
1210  2         0            000079751.79  0           -000241189.45
1213  2         000154520.73 0             0           -000227258.71
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need help on merging two records from same file

Postby BillyBoyo » Thu Sep 20, 2012 1:33 am

So, it looks like your key is Code# and Flag1.

Still, how do you determine which amount is which? You show values in amounts 1, 2 and 4, but flag2 values of only 1 and 4?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Need help on merging two records from same file

Postby Dhiren » Thu Sep 20, 2012 2:03 am

Hi Billy,
Yes the key is the combination of Code# nd Flag1.
For every key record, we check the value of Flag2 and the based on the value of Flag2, the amount value is written to either Amt 1 or Amt 2 or Amt 3 or Amt4.
In the preceding example, For Key value 1210 1 we have one row with Flag2 value of '4' therefore the Amount4 is written.
For key value of 1213 2, we have two Flag2 values (1 and 4) therefore the output shows Amt1 and Amt4 populated.

Thanks.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need help on merging two records from same file

Postby BillyBoyo » Thu Sep 20, 2012 2:41 am

And for 1210 2, where your have amounts 2 and 4?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Need help on merging two records from same file

Postby Dhiren » Thu Sep 20, 2012 2:46 am

For 1210 2 , we should have Amt1 and Amt4 based on Flag2 values of 1 and 4.
Dhiren
 
Posts: 12
Joined: Wed Sep 19, 2012 8:54 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post