Calculate the total 4 field based on the conditions

Shell Programming and Scripting
1

Please help me to write a script
Match with ACNO & NAME if it matched calculate the total val1 val2 val3 and val4 and GT is total of ACNO wise.please check the output

Table
-----------------

1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
1001|AND|NAC|40|50|40|50
1001|AND|NAC|20|70|20|10
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
1001|PAND|NAC|40|50|40|50
1001|PAND|NAC|20|70|20|10
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40

Output----

ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40
toatl         |20|40|60|80
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
toatl       |20|40|60|80 
GT          |40|80|120|160
1001|PAND|NAC|40|50|40|50
1001|PAND|NAC|20|70|20|10
total        |60|120|60|60
1001|AND|NAC|40|50|40|50
1001|AND|NAC|20|70|20|10
total       |60|120|60|60
GT          |120|240|120|120
1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
total        |40|60|40|70
GT           |40|60|40|70
2

What have you tried to solve this problem?

3

sorry for getting replay late Busy with other task

awk -F\| '
FNR==1  {comp=$1 and $2
        }
comp!=$1 && $2 {print "Total", add3, add4,add5,add6; 
        }
        {
         add3+=$3
         add4+=$4
         add5+=$5
         add6+=$6
        }
ACNO=$1 {
         gt1+=$3
         gt2+=$4
         gt3+=$5
         gt4+=$6
         }
END     {print "Total", add1,add2,add3,add4
         print "   Grand Total","   ", gt1, gt2,gt3,gt4
        }
'

Getting error not able to execute please help me out this issue

4

What error are you getting?
What file is this script supposed to process?
What operating system are you using?
What shell are you using?

5

Have a look into How to add subtotal and total according 3rd field mentioned below table?

6

Match with ACNO & NAME if it matched calculate the total val1 val2 val3 and val4 and GT is total of ACNO wise.Using AIX Operating system and KSh shell
for better use this table

ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4
1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
1000|AND|NAC|40|50|40|50
1000|AND|NAC|20|70|20|10
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
1005|ADU|CAN|10|20|30|40
1005|ADU|CAN|10|20|30|40

Written one script which is add the value based on the second filed condition but i need compare both the 2fileds which is $1 and $2 and the Gt will be sumation of all records for $1 .

sort -t "|" -k2,2 file | awk -F\| '
FNR==1  {CMP=$2
        }
CMP!=$2 {print "Total", TMP ,add1, add2, add3, add4; add1=add2=add3=add4=0
        }
        {CMP=$2
         add1+=$4
         add2+=$5
         add3+=$6
         add4+=$7
         gt1+=$4
         gt2+=$5
         gt3+=$6
         gt4+=$7
        }
1
END     {print "Total", TMP, add1, add2, add3, add4; add1=add2=add3=add4=0
         print "   Grand for ACCN wise","   ", gt1, gt2, gt3,gt4
        }
' OFS="|"

Need this output as mentioned below table.

1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
Total        20|40|60|80
1000|AND|NAC|20|70|20|10
1000|AND|NAC|40|50|40|50
Total       |60|120|60|60
Grand for ACCN wise|80|160|120|140
1005|ADU|CAN|10|20|30|40
1005|ADU|CAN|10|20|30|40
Total        20|40|60|80
1005|ANDP|ACN|20|10|30|40
1005|ANDP|ACN|20|50|10|30
Total         40|60|40|70
Grand for ACCN wise|60|100|100|150
7

Maybe something like this would come closer to what you want:

#!/bin/ksh
IAm=${0##*/}
tmpf="$IAm.$$"
awk -F'|' -v tmpf="$tmpf" '
function pt(last) {
	if(t1 != "") {
		printf("%-*s" FS "%d" FS "%d" FS "%d" FS "%d\n",
		    lw, "ACNO Total", t1, t2, t3, t4)
		g1 += t1
		g2 += t2
		g3 += t3
		g4 += t4
		if(last)
			printf("%-*s" FS "%d" FS "%d" FS "%d" FS "%d\n",
			    lw, "Grand Total", g1, g2, g3, g4)
		else	t1 = t2 = t3 = t4 = 0
	}
}
{	v1[key = $1 FS $2] += $4
	v2[key] += $5
	v3[key] += $6
	v4[key] += $7
	line[key, ++linec[key]] = $0
	if(length(key FS $3) > totalwidth[key])
		totalwidth[key] = length(key FS $3)
}
END {	if(NR == 0)
		exit
	sort_cmd = "sort -t\"|\" -k1,1n -k2,2 > " tmpf
	for(key in v1)
		print key | sort_cmd
	close(sort_cmd)
	print "ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4"
	while((rc = (getline < tmpf)) == 1) {
		if ($1 != acno) {
			pt(0)
			acno = $1
		}
		key = $0
		for(i = 1; i <= linec[key]; i++)
			print line[key, i]
		printf("%-*s" FS "%d" FS "%d" FS "%d" FS "%d\n",
		    lw = totalwidth[key], "Total",
		    v1[key], v2[key], v3[key], v4[key])
		t1 += v1[key]
		t2 += v2[key]
		t3 += v3[key]
		t4 += v4[key]
	}
	close(tmpf)
	pt(1)
}' file
rm -f "$tmpf"

which produces the following output if the file named file contains the sample data shown in post #1 in this thread:

ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
Total       |20|40|60|80
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40
Total        |20|40|60|80
ACNO Total   |40|80|120|160
1001|AND|NAC|40|50|40|50
1001|AND|NAC|20|70|20|10
Total       |60|120|60|60
1001|PAND|NAC|40|50|40|50
1001|PAND|NAC|20|70|20|10
Total        |60|120|60|60
ACNO Total   |120|240|120|120
1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
Total        |40|60|40|70
ACNO Total   |40|60|40|70
Grand Total  |200|380|280|350

Note that the first part of this script was modified to fix the problem RudiC noted in post #8.

8

@Don Cragun: Are you sure $tmpf is defined on shell level (to be used by rm )?

1 Like
9

Ouch! No. The line:

awk -F'|' -v tmpf="$IAm.$$" '

should be changed to:

tmpf="$IAm.$$"
awk -F'|' -v tmpf="$tmpf" '

I'll fix my earlier post.

Thanks.

11

1.Please remove the only the Grand total record logic in the script Trying to remove the logic but getting error.
Script is displaying right output for Total and ACNO Total(ACNO wise) (please use OFMT because it is floating numbers) .
2.Am new in awk programming .Could you please explain little bit about this script.

Output-----
ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
Total       |20|40|60|80
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40
Total        |20|40|60|80
ACNO Total   |40|80|120|160
1001|AND|NAC|40|50|40|50
1001|AND|NAC|20|70|20|10
Total       |60|120|60|60
1001|PAND|NAC|40|50|40|50
1001|PAND|NAC|20|70|20|10
Total        |60|120|60|60
ACNO Total   |120|240|120|120
1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
Total        |40|60|40|70
ACNO Total   |40|60|40|70
12

Maybe this will help?

#!/bin/ksh
IAm=${0##*/}			# Get basename of this script.
tmpf="$IAm.$$"			# Create name for temp file to be used to sort
				# keys.
awk -F'|' -v tmpf="$tmpf" '	# Use awk to evaluate the following wcript with
				# FS (the input field separator) set to "|" and
				# with the awk variable tmpf set to the value of
				# the shell variable with the same name.
# Define function to print account number totals and clear the accumulated
# totals.
function pt() {
	# This function will be called whenever the 1st field (account #)
	# changes.  So, we do not want to print anything the first time we are
	# called.
	if(printed++) {
		# Print account total.
		printf("%-*s" FS OFMT FS OFMT FS OFMT FS OFMT "\n",
		    lw, "ACNO Total", t1, t2, t3, t4)
		# Clear totals in preparation for next account.
		t1 = t2 = t3 = t4 = 0
	}
}
{	# For each linee read from the input file(s):
	#	set key to the account number "|" name,
	#	set v1[key], v2[key], v3[key], and v4[key] to the sum of the 
	#		values seen for the corresponding fields so far,
	#	increment the number of lines seen with this key (linec[key]),
	#	save the input line so it can be printed as is with other lines
	#		for this key,
	#	and keep track of the widest combination of key and town seen
	#		for this key.  The totalwidth[key] value will be used
	#		align the values in the account total output with the
	#		values on the widest line for that key.
	v1[key = $1 FS $2] += $4
	v2[key] += $5
	v3[key] += $6
	v4[key] += $7
	line[key, ++linec[key]] = $0
	if(length(key FS $3) > totalwidth[key])
		totalwidth[key] = length(key FS $3)
}
END {	# After we have read all of the input file(s) given, process the data we
	# have accumulated.
	if(NR == 0)
		# If no input lines were found, we are done.
		exit
	# Sort the list of keys found in the input file(s) using the account
	# number as a numeric value as the primary key and the name as the
	# secondary key saving the sorted output in the temporary file named by
	# tmpf.
	sort_cmd = "sort -t\"|\" -k1,1n -k2,2 > " tmpf
	for(key in v1)
		print key | sort_cmd
	# Tell the system we have written all of the keys we have, to sort the
	# keys we have written, and to save the results in our temporary file.
	close(sort_cmd)
	# Print the output file header.
	print "ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4"
	# Read the keys from the (sorted) temporary file (still using "|" as
	# the field separator).
	while((rc = (getline < tmpf)) == 1) {
		# If the account # on this line is not the same as the account #
		# seen on the previous line, print the totals for the previous
		# account # and save the new account #.
		if ($1 != acno) {
			pt()
			acno = $1
		}
		# Save the key.  (We could just use $0, but key is more
		# descriptive.)
		key = $0
		# Print all of the input lines found for this key in the order
		# in which they appeared in the input file(s).
		for(i = 1; i <= linec[key]; i++)
			print line[key, i]
		# Print the sums for this key.
		printf("%-*s" FS OFMT FS OFMT FS OFMT FS OFMT "\n",
		    lw = totalwidth[key], "Total",
		    v1[key], v2[key], v3[key], v4[key])
		# Add the sums for this key to the sums for this account #.
		t1 += v1[key]
		t2 += v2[key]
		t3 += v3[key]
		t4 += v4[key]
	}
	# Tell the system we are done with the temporary file.
	close(tmpf)
	# And, print the total line for the last account # we encountered.
	pt()
}' file # End the awk script and list the file(s) to be processed.
# Remove the temporary file we created.
rm -f "$tmpf"
14
15

From the text you downloaded, it looks like you ignored my post #11 in this thread. I have made several changes from the script I supplied in that post to meet your new requirements:

  1. Since the output format you say you want for account/name subtotals and account totals doesn't match the sample output you have shown us in post #12 in this thread, I have made up my own formats for these lines that I hope will be something you can use.
  2. This version of the script sorts the entire input file before calling awk and prints account/name subtotals and account totals as data is read (instead of reading your unsorted data, sorting just the keys, and printing everything at the end).
  3. This version does not make any attempt to line up output columns (since the sample output you said you want is not aligned in any way.
  4. This version prints the account number on account number total lines as requested and prints the account number and name subtotal lines.
  5. This version uses OFMT as requested (but uses %.2f instead of the default %6g since the sample data you downloaded has all values in the data fields presented with two digits after the decimal point).
  6. This version accepts a single file operand to name the input file. If called with no operands, it will default to using a file named file .
  7. As long as the number of fields is constant within your input file, this version can handle any number of input fields.

Hopefully, this will produce something closer to what you want:

#!/bin/ksh
# Sort input file (default to "file" if no parameters are given) by (numeric)
# account number and (alphanumeric) account name.
sort -t'|' -k1,1n -k2,2 "${1:-file}" |
awk -F'|' '	# Use awk to evaluate the following wcript with FS (the input
		# field separator) set to "|".
# Define function to print account number/account name subtotals and account
# number totals.
function pt(level,	i) {
	# Print account number/account name subtotal header.
	printf("%d%s%s%sTotal%s", acno, OFS, name, OFS, OFS)
	# Print account/name subtotals, add subtotals to account totals, and
	# clear subtotals in preparation for next name.
	for(i = 4; i <= nf; i++) {
		printf(OFMT "%s", s, (i == nf) ? RS : OFS)
		t += s
		s = 0
	}
	if(level) {
		# Print account totals too.
		# Print account total header.
		printf("%d%s-%sACNO Total%s", acno, OFS, OFS, OFS)
		# Print account totals and clear in preparation for next
		# account.
		for(i = 4; i <= nf; i++) {
			printf(OFMT "%s", t, (i == nf) ? RS : OFS)
			t = 0
		}
	}
}
# Set defaults before reading the first line form the sorted input file.
BEGIN { # Change default sum output format to 2 decimal places.
	OFMT = "%.2f"
	# Set output field separator to match input field separator.
	OFS = FS
}
FNR == 1 {
	# Grab the field count from the 1st input line.
	nf = NF
	# Grab account # and name from the 1st input line.
	acno = $1
	name = $2
}
{	# Process a line from our sorted input file.
	if(acno != $1) {
		# The account number has changed; print last account number/
		# account name subtotals and account number totals.
		pt(1)
		# Grab new account number and account names from this line.
		acno = $1
		name = $2
	}
	if(name != $2) {
		# If the account name has changed (but the account number is the
		# same), print account number/account name subtotals.
		pt(0)
		# Grab the account name from this line.
		name = $2
	}
	# Add current line data to account name subtotals.
	for(i = 4; i <= nf; i++)
		s += $i
}
1	# Print the current line.
END {	# Print the total line for the last account name and account number.
	pt(1)
}'	# End the awk script.

If a file named file contains the sample data you provided in post #1 in this thread and you invoke it as:

./scriptname

it produces the output:

1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
1000|ADU|Total|20.00|40.00|60.00|80.00
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40
1000|NADU|Total|20.00|40.00|60.00|80.00
1000|-|ACNO Total|40.00|80.00|120.00|160.00
1001|AND|NAC|20|70|20|10
1001|AND|NAC|40|50|40|50
1001|AND|Total|60.00|120.00|60.00|60.00
1001|PAND|NAC|20|70|20|10
1001|PAND|NAC|40|50|40|50
1001|PAND|Total|60.00|120.00|60.00|60.00
1001|-|ACNO Total|120.00|240.00|120.00|120.00
1005|ANDP|ACN|20|10|30|40
1005|ANDP|ACN|20|50|10|30
1005|ANDP|Total|40.00|60.00|40.00|70.00
1005|-|ACNO Total|40.00|60.00|40.00|70.00

and, if you invoke it as:
./scriptname file2
where file2 is a file containing the data you downloaded with your post #12 (with DOS <carriage-return> characters removed and a <newline> character added to terminate the last line in your input file), it produces the output:

1000|ADU|CAN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1000|ADU|CAN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1000|ADU|Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
1000|NADU|CAN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1000|NADU|CAN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1000|NADU|Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
1000|-|ACNO Total|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24
1001|AND|NAC|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1001|AND|NAC|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1001|AND|Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
1001|PAND|NAC|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1001|PAND|NAC|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1001|PAND|Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
1001|-|ACNO Total|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24|501362.24|498342.24|506194.24
1005|ANDP|ACN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1005|ANDP|ACN|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56|125340.56|124585.56|126548.56
1005|ANDP|Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
1005|-|ACNO Total|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12|250681.12|249171.12|253097.12
16

Hi Don,
Many many thanks a lot for ur replay. Issue got resolved
This is the best side never seen before and also i wll recomanded this side to my friends so that they can get help from you gays
Please share usefull docs for awk programming if u have.

17

I'm glad my replies helped.

You can show your appreciation for posts that helped you solve your problem by hitting the :b:Thanks button in the lower left corner of the post.

A good place to look for documentation for any utility on your system is the man page for that utility. In this case that would mean using the command:
man awk

1 Like
18

Getting issue while print the output header

print "ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4.........V25"
19

Sorry. I forgot to print the header in the latest revision of the code. Printing a header with a variable number of fields is just like printing a variable number of subtotals or totals.

Change the code that processes the 1st line of input from:

FNR == 1 {
	# Grab the field count from the 1st input line.
	nf = NF
	# Grab account # and name from the 1st input line.
	acno = $1
	name = $2
}

to also print the header:

FNR == 1 {
	# Grab the field count from the 1st input line.
	nf = NF
	# Grab account # and name from the 1st input line.
	acno = $1
	name = $2
	# Print the header line.
	printf("ACNO%sNAME%sTOWN%s", OFS, OFS, OFS)
	for(i = 1; i <= nf - 3; i++)
		printf("VAL%d%s", i, (i == nf - 3) ? RS : OFS)
}
20

Sorry i should mention while this program print the output as per my requirements

21

Hi Don,
some changes in the requirements input file has no header while display output I want to insert header below mentioned
line
ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4|VAL5|VAL6|VAL7|VAL7|VAL9|VAL10.........V25" please help me on this

22

Please show us a sample of the output your script is producing now and an updated sample output including the exact output you now want to produce (both in CODE tags).

And, please show us how you have attempted to modify your script to make it produce the new output you want to add (also in CODE tags).