0

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
ADARO INDO    AA_Der        126.50        1128.18
ADARO INDO    AB_FX         3.00          78.00
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
ADARO         AA            126.50       1128.18
ADARO         AB            3.00         78.00
Hun Holds     AA            2492.84     10592.97
#To sort & get the unique names

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

while read file;
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

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

1
Contributor
1
Reply
2
Views
6 Years
Discussion Span
Last Post by mkbuster
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.