I need to sum up the values of 3rd column and 4th coulmn based on the following conditions

1) If the 1st column (i.e ctpy) of the record matches with any other record &&
first two character of the 2nd column (ety), matches with first two character matches the 2nd column of any other record

then i need to the add all the sum_m , sum_v of the matching reocrds respectively

``````ctpy          ety           sum_m         sum_v
A             AA            2343.60       234.28
A BIRLA       AA_Fx         1943.60       1629.28
A BIRLA       AA_Der        23.00         0.00
AARTI BOM     AA_FX         959.16        558.23
AARTI BOM     AA_Der        854.66        654.72
Hun Holds     AA            2492.84       10592.97``````

Expected Output:

``````ctpy          ety           sum_m         sum_v
A             AA            2343.60      234.28
A BIRLA       AA            1966.60      1629.28
AARTI BOM     AA            1813.82      1212.95
Hun Holds     AA            2492.84     10592.97``````
``````#To sort & get the unique names

cut -d'|' -f1 infile.txt | sort | uniq > sort_file.txt

do
#grep to get the records from infile
grep "\$file" infile.txt > out1.txt

value=`awk '{for (i = 1; i <= 1; i++)

#taking substring to cmpare the 2nd field
sub_entity=substr(\$2,1,3);

if (\$1 == ctpy && sub_entity == entity)
{
sum_m = sum_m + \$4;
sum_p = sum_p + \$3;
ety = sub_ety;
}
else if (\$1 == ctpy && sub_entity != entity)
{
sum_m = sum_m;
sum_p = sum_p;
ety = ety;
}
else
{
sum_m = \$4;
sum_p = \$3;
ety = sub_ety;
}
ctpy=\$1;

}
END
{printf("%s|%s|%.4f|%.4f\n",ctpy,ety,sum_m,sum_p)}' FS="|" out1.txt`

echo \$value>> \$TMP6
done < sort_file.txt``````

Got output. Thank u..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.