Computing sums & formatting ... 2

[TechMaria]

Hi,

I have the following file :

ROGER Jan 1000 4000 5000 20 80
ROGER Feb 2000 2000 4000 50 50
ROGER Mar 3000 3000 6000 50 50
TAMMY Jan 1000 4000 5000 20 80
TAMMY Feb 2000 2000 4000 50 50
TAMMY Mar 3000 3000 6000 50 50
TAMMY Apr 3000 3000 6000 50 50

Can I get this converted to the following format :

Name Month Loan1 Loan2 Total %Loan1 %Loan2
---- ----- ----- ----- ----- ------ ------
ROGER Jan 1,000 4,000 5,000 20 80
Feb 2,000 2,000 4,000 50 50
Mar 3,000 3,000 6,000 50 50
---- ---- -----
Sum: 6,000 9,000 15,000

TAMMY Jan 1,000 4,000 5,000 20 80
Feb 2,000 2,000 4,000 50 50
Mar 3,000 3,000 6,000 50 50
Apr 3,000 3,000 6,000 50 50
---- ---- -----
Sum: 9,000 12,000 21,000

---- ----- -----
Grant Total: 15,000 21,000 36,000


1) Forgive me if the formatting doesn't look nice when posted. But I hope you get my requirement ...

2) The column entries are dynamic. There could be more entries under Name, Month columns ...

Thanx in advance !

-Maria

 

[guggach]

are you posting a home-work ?
a excellent book:
The AWK Programming Language, Addison-Wesley Copyright 1988
by: Alfred V. Aho, Brian W. Kernigan, Peter J. Weinberger

don't forget, RTFMP guggach

 

[pete91z]

The Unix Scripting and AWK forums are more helpful for this type of question.

 

[dblair]

import to excel ???

 

[Ygor]

I hope that this awk script will encourage you to learn awk. If you have any problems then use the awk forum: forum271.

BEGIN {
fmt="%-8.8s%-5.5s %6.6s %6.6s %6.6s %6.6s %6.6s\n"
printf fmt, "Name", "Month", "Loan1", "Loan2", "Total", "%Loan1", "%Loan2";
printf fmt, "----", "-----", "-----", "-----", "-----", "------", "------";
}
function ic(val) {#inserts commas as thousand separators
rtn=""; d=0;
for(c=length(val); c>0; c--)
rtn = (++d%3==0 ? "," : "") substr(val,c,1) rtn;
return rtn;
}
function print_line() {
printf fmt, ($1==prev ? "" : $1), $2, ic($3), ic($4), ic($5), $6, $7;
s[1]+=$3; g[1]+=$3;
s[2]+=$4; g[2]+=$4;
s[3]+=$5; g[3]+=$5;
}
function print_sub_tot() {
printf fmt, "", "", "-----", "-----", "-----", "", "";
printf fmt "\n", "Sum:", "", ic(s[1]), ic(s[2]), ic(s[3]), "", "";
s[1]=s[2]=s[3]=0;
}
function print_grand_tot() {
printf fmt, "", "", "-----", "-----", "-----", "", "";
printf fmt, "Grant to", "tal:", ic(g[1]), ic(g[2]), ic(g[3]), "", "";
}
{#main
if ($1 != prev [&][&] NR > 1)
print_sub_tot();
print_line();
prev = $1;
}
END {
print_sub_tot();
print_grand_tot();
}

Tested on the sample data....
[tt]
Name Month Loan1 Loan2 Total %Loan1 %Loan2
---- ----- ----- ----- ----- ------ ------
ROGER Jan 1,000 4,000 5,000 20 80
Feb 2,000 2,000 4,000 50 50
Mar 3,000 3,000 6,000 50 50
----- ----- -----
Sum: 6,000 9,000 15,000

TAMMY Jan 1,000 4,000 5,000 20 80
Feb 2,000 2,000 4,000 50 50
Mar 3,000 3,000 6,000 50 50
Apr 3,000 3,000 6,000 50 50
----- ----- -----
Sum: 9,000 12,000 21,000

----- ----- -----
Grant total: 15,000 21,000 36,000[/tt]

 

[TechMaria]

Ygor !

I sure don't know how many stars I should give for this post !... (actually I was waiting for your response !)

As usual, your solution rocks ... and it works like a charm ... I am definitely in the process of learning awk and this would definitely help me get jumpstarted !

Just a couple of queries ...

1)If the values under columns 3,4,5 are not 4 digits, then it prints an extra comma in front of a 3 digit number...

2)If the column entries under column 2 (Month) are of varying width, then output is slightly skewed ...

If the file is like :

TAMMY Jan 1000 4000 5000 20 80
TAMMY February 200 2000 4000 50 50

Then it prints the following :

TAMMY Jan 1,000 4,000 5,000 20 80
February ,200 2,000 4,000 50 50

Is there a way to print the output as below :

TAMMY Jan 1,000 4,000 5,000 20 80
February 200 2,000 4,000 50 50

All, Thanx a great lot for the responses !

-Maria

 

[Ygor]

Answers...

1) I hadn't put much thought into the ic() function. You will need to use:
rtn = substr(val,c,1) (d++%3==0 [&][&] d>1 ? "," : "") rtn;

2) Month names should have been truncated to five characters. Try adjusting the format string.