Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Calculate the total 4 field based on the conditions

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Calculate the total 4 field based on the conditions
# 8  
Old 11-02-2015
@Don Cragun: Are you sure $tmpf is defined on shell level (to be used by rm)?
This User Gave Thanks to RudiC For This Post:
Don Cragun
# 9  
Old 11-02-2015
Quote:
Originally Posted by RudiC
@Don Cragun: Are you sure $tmpf is defined on shell level (to be used by rm)?
Ouch! No. The line:
Code:
awk -F'|' -v tmpf="$IAm.$$" '

should be changed to:
Code:
tmpf="$IAm.$$"
awk -F'|' -v tmpf="$tmpf" '

I'll fix my earlier post.

Thanks.
# 10  
Old 11-03-2015
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.
Code:
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

Last edited by kalia4u; 11-03-2015 at 07:21 AM..
# 11  
Old 11-03-2015
Maybe this will help?
Code:
#!/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"

# 12  
Old 11-04-2015
Quote:
Originally Posted by kalia4u
Thanks For yours replay
Sorry for bother you agian i missed few things .yes it is matching with our requirements i cann’t share my input file due to some issue only thing i can apply this logic as per ours requirements
Is it possibble while display the records looks below mentioned table .
1.when dislpay the subtotal and total it will be display Accn along with the subtotal and the total and that should be display third field positions
2.I have to caluclate subtotal and total for 15 fields all have 6 digits numbers (Should i use for loop for 15 fields) please let me know should i mention 15 times OFS also for the same as per our requirements .PFA attached input file and my program.
Code:
ACNO|NAME|TOWN|VAL1|VAL2|VAL3|VAL4
1000|ADU|CAN|10|20|30|40
1000|ADU|CAN|10|20|30|40
1000|   |Total|20|40|60|80
1000|NADU|CAN|10|20|30|40
1000|NADU|CAN|10|20|30|40
1000|    |Total|20|40|60|80
1000|    |ACNO Total 40|80|120|160
1001|AND|NAC|40|50|40|50
1001|AND|NAC|20|70|20|10
1001|  |Total|60|120|60|60
1001|PAND|NAC|40|50|40|50
1001|PAND|NAC|20|70|20|10
1001|    |Tota|60|120|60|60
1001|    |ACNO Total|120|240|120|120
1005|ANDP|ACN|20|50|10|30
1005|ANDP|ACN|20|10|30|40
1005|    |Total |40|60|40|70
ACNO Total   |40|60|40|70

task.txt (3.3 KB) 
# 13  
Old 11-04-2015
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:
Code:
#!/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], (i == nf) ? RS : OFS)
		t[i] += s[i]
		s[i] = 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], (i == nf) ? RS : OFS)
			t[i] = 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] += $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:
Code:
./scriptname

it produces the output:
Code:
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:
Code:
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

# 14  
Old 11-05-2015
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.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to assign points to variables based on conditions and update specific field

I have been reading old posts and trying to come up with a solution for the below: Use a tab-delimited input file to assign point to variables that are used to update a specific field, Rank. I really couldn't find too much in the way of assigning points to variable, but made an attempt at an awk... (4 Replies)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

awk to calculate total and percent off field in file

Trying to use awk to print the lines in file that have either REF or SNV in $3, add a header line, sort by $4 in numerical order. The below code does that already, but where I am stuck is on the last part where the total lines are counted and printed under Total_Targets, under Targets_less_than is... (4 Replies)
Discussion started by: cmccabe
4 Replies

3. Shell Programming and Scripting

Calculate the total

Hi All , I have the following script as below , I tried to modify to meet the requirement , could someone help ? very thanks ================================================================================================ while read STR NAME; do Total=0 MyString="$STR" GetData () {... (18 Replies)
Discussion started by: ust3
18 Replies

4. Shell Programming and Scripting

Calculate total value from a row

HI I have a file # cat marks.txt MARKS LIST 2013 Name english french chinese latin total_marks wer 34 45 67 23 wqa 12 39 10 56 wsy 23 90 23 78 Now i need to find the total marks of each student using... (11 Replies)
Discussion started by: Priya Amaresh
11 Replies

5. Shell Programming and Scripting

Adding total of first field for each number in the second field

Dears, I need a script or command which can find the unique number from the second filed and against that number it adds the total of first field . 17215630 , 0 907043 ,1 201050 ,10 394149 ,4 1964 ,9 17215630, 0 907043 ,1 201050, 10 394149 ,4 1964 ,9 1234234, 55 23 ,100 33 ,67 ... (2 Replies)
Discussion started by: shary
2 Replies

6. Shell Programming and Scripting

Extract file records based on some field conditions

Hello Friends, I have a file(InputFile.csv) with the following columns(the columns are pipe-delimited): ColA|ColB|ColC|ColD|ColE|ColF Now for this file, I have to get those records which fulfil the following condition: If "ColB" is NOT NULL and "ColD" has values one of the following... (9 Replies)
Discussion started by: mehimadri
9 Replies

7. Shell Programming and Scripting

Calculate total space, total used space and total free space in filesystem names matching keyword

Good afternoon! Im new at scripting and Im trying to write a script to calculate total space, total used space and total free space in filesystem names matching a keyword (in this one we will use keyword virginia). Please dont be mean or harsh, like I said Im new and trying my best. Scripting... (4 Replies)
Discussion started by: bigben1220
4 Replies

8. Shell Programming and Scripting

Calculate total sum from a file

The file content is dynamic and using this format: name1 number1 name2 number2 name3 number3 name4 number4 .................... Need a smooth way to calculate the sum of all the numbers in that file (number1 + number2 + number3 + number4........ = total ) (11 Replies)
Discussion started by: TehOne
11 Replies

9. Shell Programming and Scripting

awk script to calculate total

Hi First field is the Record Type. A Record Type 5 can have multiple Record Type 6's before another Record Type 5 appears. I want to calculate the total of fields at position 8-11 on Record type 6 when Record Type 5 has a field at position 11-14 equals to '2222'. then it should delete the lines... (2 Replies)
Discussion started by: appsguy616
2 Replies
Login or Register to Ask a Question