Page 1 of 2

Need help on merging two records from same file

PostPosted: Wed Sep 19, 2012 11:01 pm
by Dhiren
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.

Re: Need help on merging two records from same file

PostPosted: Wed Sep 19, 2012 11:11 pm
by NicC
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?

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 12:06 am
by Dhiren
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.

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 12:17 am
by Akatsukami
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 ?

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 12:20 am
by BillyBoyo
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.

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 12:31 am
by Dhiren
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

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 1:33 am
by BillyBoyo
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?

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 2:03 am
by Dhiren
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.

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 2:41 am
by BillyBoyo
And for 1210 2, where your have amounts 2 and 4?

Re: Need help on merging two records from same file

PostPosted: Thu Sep 20, 2012 2:46 am
by Dhiren
For 1210 2 , we should have Amt1 and Amt4 based on Flag2 values of 1 and 4.