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
# 1  
Old 10-29-2015
Calculate the total 4 field based on the conditions
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
-----------------
Code:
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----


Code:
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

Moderator's Comments:
Mod Comment The CODE tags need to be positioned as:
[CODE]sample input or output or code[/CODE]
not as:
[CODE][/CODE]sample input or output or code
Last edited by Don Cragun; 10-29-2015 at 04:21 PM.. Reason: Fix CODE tags.
# 2  
Old 10-29-2015
What have you tried to solve this problem?
# 3  
Old 11-01-2015
sorry for getting replay late Busy with other task
Code:
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

Moderator's Comments:
Mod Comment Please pay attention at the moderator's comment you got:
The CODE tags need to be positioned as:
Code:
sample input or output or code

not as:
sample input or output or code
Last edited by vbe; 11-01-2015 at 12:06 PM..
# 4  
Old 11-01-2015
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  
Old 11-01-2015
Have a look into How to add subtotal and total according 3rd field mentioned below table?
# 6  
Old 11-02-2015
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
Code:
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 .
Code:
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.
Code:
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

Last edited by kalia4u; 11-02-2015 at 08:37 AM..
# 7  
Old 11-02-2015
Maybe something like this would come closer to what you want:
Code:
#!/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:
Code:
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.
Last edited by Don Cragun; 11-02-2015 at 03:17 PM.. Reason: Define tmpf for use by shell.
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