summing up of different colums on a unique column

[varsha]

Hi,

The following sample output of my file.

id debit credit
============================================
57571 0.00 22250.00 22250.00
57571 0.00 22250.00 22250.00

85789 0.00 17500.00 0.00
85789 18500.00 0.00 0.00


1. I want to take get the net amount on id column. If the id is same on the debit and credit column net amount to be arrived (positive).

2. If the last column has consecutive same value one amount tobe deleted and one entire row is to be eliminated.

The output should be:

57571 0.00 22250.00 0.00
85789 1000.00 0.00 0.00


I dont have any idea of arriving this. Help is appreciated. Thanking you.

[varsha]

Help me please...!!

[Janis Papanagnou]

Am 08.03.2013 15:59, schrieb varsha:
> On Friday, 8 March 2013 05:38:00 UTC+5:30, varsha wrote:
>> Hi,
>>

[...]

>>
>>
>> I dont have any idea of arriving this. Help is appreciated. Thanking you.
>
>
> Help me please...!!
>

If you haven't got any answer yet it's likely that you formulated your
question not clear enough and/or haven't presented appropriate samples
to make readers understand the logic that you expect.

Janis

[varsha]

Ok. Here is some more data.

id debit credit column from another file

89810 8000.00 0.00 0.00
89905 3000.00 0.00 0.00
90875 17000.00 0.00 0.00
91274 0.00 15750.00 0.00
91274 15760.00 0.00 0.00
91307 0.00 10250.00 0.00
91715 0.00 12250.00 0.00
91715 7750.00 0.00 0.00
92184 0.00 13500.00 0.00
92184 8500.00 0.00 0.00
92223 0.00 7000.00 0.00
92249 0.00 23750.00 0.00
92249 22290.00 0.00 0.00
92382 0.00 10500.00 0.00
92538 0.00 29500.00 0.00
92538 16000.00 0.00 0.00
92971 0.00 6500.00 6500.00 C
92971 0.00 6500.00 6500.00 C
96735 0.00 920.00 0.00
96735 930.00 0.00 0.00
50833 13560.00 0.00 0.00
51737 11250.00 0.00 0.00


what i have done is, i have joined two files on field one basis(using shell join -t" " -a1).

1.When there are two similar records in file1 and there is only one record in file2 it is matching both records of file1. The effect of this...

92971 0.00 6500.00 6500.00 C
92971 0.00 6500.00 6500.00 C

so i want to modify it as:

92971 0.00 6500.00 6500.00 C
92971 0.00 6500.00 0.00


2.when id is same i want to post the difference of both(debit,credit)

91274 0.00 15750.00 0.00
91274 15760.00 0.00 0.00

as

91274 10.00 0.00 0.00


thank you

[Ed Morton]

On 3/8/2013 5:18 PM, varsha wrote:
> On Friday, 8 March 2013 22:34:10 UTC+5:30, Janis Papanagnou wrote:
>> Am 08.03.2013 15:59, schrieb varsha:
>>
>>> On Friday, 8 March 2013 05:38:00 UTC+5:30, varsha wrote:
>>
>>>> Hi,
>>
>>>>
>>
>> [...]
>>
>>>>
>>
>>>>
>>
>>>> I dont have any idea of arriving this. Help is appreciated. Thanking you.
>>
>>>
>>
>>>
>>
>>> Help me please...!!
>>
>>>
>>
>>
>>
>> If you haven't got any answer yet it's likely that you formulated your
>>
>> question not clear enough and/or haven't presented appropriate samples
>>
>> to make readers understand the logic that you expect.
>>
>>
>>
>> Janis
>
> Ok. Here is some more data.

Maybe others will be able to figure out what you want but I can't. Don't give us
the history of how you created the sample input file (joined 2 files on....) and
don't just throw more and more data at us. Simply show a SMALL representative
input file, the expected output from that input and a brief description of why
that is the expected output without reference to whatever files you joined
previously to create your sample input - just talk about the input file(s) as
far as the awk script is concerned.

Ed.

[Dave Gibson]

Process the first file before joining the two.

>
>
> 2.when id is same i want to post the difference of both(debit,credit)
>
> 91274 0.00 15750.00 0.00
> 91274 15760.00 0.00 0.00
>
> as
>
> 91274 10.00 0.00 0.00

Assuming multiple credit and/or debit entries should accumulate.

awk '
{
id[$1]
debit[$1] += $2
credit[$1] += $3
}

END {
for (n in id) {
c = credit[n] - debit[n]
d = 0
if (c < 0) {
d -= c
c = 0
}
printf "%s %.2f %.2f\n", n, d, c
}
}
' file1 > file1.acc

Replace join with something like this:

awk '
NR == FNR {
n = $1
$1 = ""
extra[n] = $0
next
}

!($1 in extra) { extra[$1] = "0.00" }

{
print $0 " " extra[$1]
extra[$1] = "0.00"
}
' file2 file1.acc

The formatting will need to be sorted out.

[varsha]

On Friday, 8 March 2013 05:38:00 UTC+5:30, varsha wrote:

Thank you very much. The result is what I needed.
To join two fields I have used NR==FNR procedure earlier but there is one problem it will only matching the matched lines when we compare higher number of records file with lower records. The join -a will give all the records. Is there any awk script for this?

[Dave Gibson]

(referring to the second awk script in
Message-ID: <87p61ax...@perseus.wenlock-data.co.uk>)

There may be more records in the data file with the id and fourth
columns than in the file with the id, debit and credit columns?

awk '
NR == FNR {
n = $1
$1 = ""

a[n] = $0
next
}

{
if ($1 in a) {
n = " " a[$1]
delete a[$1]
} else
n = " 0.00"
print $0 n
}

END {
for (n in a)
print n " 0.00 0.00 " a[n]
}
' file_with_fourth_column file_with_debit_and_credit_columns