shell script to consolidate inventory report

• 10-04-2007, 04:23 AM
unix
shell script to consolidate inventory report
Hi, folks,

I'm trying to figure out a shell script to consolidate my inventory.
The report I'm having now looks like this:

Item Dept. Quantity Price
0001 A 100 5.00
0001 A 150 5.20
0001 B 500 5.15
0001 B 700 4.90
0002 A 10000 3.00
0002 A 7000 3.15
0002 A 8000 3.20

What I'm trying to do is producing a report which has a total quantity
of each item and its average price, how could I do it to get this?

0001 A 250 (5x100+5.2x150)/250
0001 B 1200 (5.15x500+4.9x700)/1200
0002 A 25000 (3x10000+3.15x7000+3.2x8000)/25000

• 10-04-2007, 04:23 AM
unix
Re: shell script to consolidate inventory report
On 2007-05-03, [email]nikonsports@gmail.com[/email] wrote:[color=blue]
> Hi, folks,
>
> I'm trying to figure out a shell script to consolidate my inventory.
> The report I'm having now looks like this:
>
> Item Dept. Quantity Price
> 0001 A 100 5.00
> 0001 A 150 5.20
> 0001 B 500 5.15
> 0001 B 700 4.90
> 0002 A 10000 3.00
> 0002 A 7000 3.15
> 0002 A 8000 3.20
>
> What I'm trying to do is producing a report which has a total quantity
> of each item and its average price, how could I do it to get this?
>
> 0001 A 250 (5x100+5.2x150)/250
> 0001 B 1200 (5.15x500+4.9x700)/1200
> 0002 A 25000 (3x10000+3.15x7000+3.2x8000)/25000[/color]

awk 'NR == 1 { next } ## skip header
{
item[\$1,\$2] = \$1
dept[\$1,\$2] = \$2
qty[\$1,\$2] += \$3
tot[\$1,\$2] += \$3 * \$4
}
END {
for ( id in item )
printf "%s\t%s\t%d\t%s\n", item[id], dept[id], qty[id],
tot[id] / qty[id]
}' "\$FILE" | sort

--
Chris F.A. Johnson, author | <http://cfaj.freeshell.org>
Shell Scripting Recipes: | My code in this post, if any,
A Problem-Solution Approach | is released under the
2005, Apress | GNU General Public Licence
• 10-04-2007, 04:23 AM
unix
Re: shell script to consolidate inventory report
On May 3, 1:54 pm, "Chris F.A. Johnson" <cfajohn...@gmail.com> wrote:[color=blue]
> On 2007-05-03, nikonspo...@gmail.com wrote:[color=green]
> > Hi, folks,[/color]
>[color=green]
> > I'm trying to figure out a shell script to consolidate my inventory.
> > The report I'm having now looks like this:[/color]
>[color=green]
> > Item Dept. Quantity Price
> > 0001 A 100 5.00
> > 0001 A 150 5.20
> > 0001 B 500 5.15
> > 0001 B 700 4.90
> > 0002 A 10000 3.00
> > 0002 A 7000 3.15
> > 0002 A 8000 3.20[/color]
>[color=green]
> > What I'm trying to do is producing a report which has a total quantity
> > of each item and its average price, how could I do it to get this?[/color]
>[color=green]
> > 0001 A 250 (5x100+5.2x150)/250
> > 0001 B 1200 (5.15x500+4.9x700)/1200
> > 0002 A 25000 (3x10000+3.15x7000+3.2x8000)/25000[/color]
>
> awk 'NR == 1 { next } ## skip header
> {
> item[\$1,\$2] = \$1
> dept[\$1,\$2] = \$2
> qty[\$1,\$2] += \$3
> tot[\$1,\$2] += \$3 * \$4}
>
> END {
> for ( id in item )
> printf "%s\t%s\t%d\t%s\n", item[id], dept[id], qty[id],
> tot[id] / qty[id]
>
> }' "\$FILE" | sort
>
> --
> Chris F.A. Johnson, author | <http://cfaj.freeshell.org>
> Shell Scripting Recipes: | My code in this post, if any,
> A Problem-Solution Approach | is released under the
> 2005, Apress | GNU General Public Licence[/color]

Thanks a lot, Chris! But now I'm seeing these errors when I test your
code on my system ...

\$ vi run.sh
1 #!/bin/sh
2
3 awk 'NR == 1 { next } ## skip header
4 {
5 item[\$1,\$2] = \$1
6 dept[\$1,\$2] = \$2
7 qty[\$1,\$2] += \$3
8 tot[\$1,\$2] += \$3 * \$4
9 }
10
11 END {
12 for ( id in item )
13 printf "%s\t%s\t%d\t%s\n", item[id], dept[id], qty[id],
14 tot[id] / qty[id]
15
16 }' "/var/tmp/test" | sort

\$ ./run.sh
awk: syntax error near line 3
awk: illegal statement near line 3
awk: syntax error near line 4
awk: illegal statement near line 4
awk: syntax error near line 5
awk: illegal statement near line 5
awk: syntax error near line 6
awk: illegal statement near line 6
awk: syntax error near line 12
awk: illegal statement near line 12

Not sure what's wrong with awk, maybe it's the square brackets?

I should have mentioned that I'm using standard Bourne shell on
Solaris, also tried bash ... same errors.

• 10-04-2007, 04:23 AM
unix
Re: shell script to consolidate inventory report
sdai wrote:[color=blue]
> On May 3, 1:54 pm, "Chris F.A. Johnson" <cfajohn...@gmail.com> wrote:
>[color=green]
>>On 2007-05-03, nikonspo...@gmail.com wrote:
>>[color=darkred]
>>>Hi, folks,[/color]
>>[color=darkred]
>>>I'm trying to figure out a shell script to consolidate my inventory.
>>>The report I'm having now looks like this:[/color]
>>[color=darkred]
>>>Item Dept. Quantity Price
>>>0001 A 100 5.00
>>>0001 A 150 5.20
>>>0001 B 500 5.15
>>>0001 B 700 4.90
>>>0002 A 10000 3.00
>>>0002 A 7000 3.15
>>>0002 A 8000 3.20[/color]
>>[color=darkred]
>>>What I'm trying to do is producing a report which has a total quantity
>>>of each item and its average price, how could I do it to get this?[/color]
>>[color=darkred]
>>>0001 A 250 (5x100+5.2x150)/250
>>>0001 B 1200 (5.15x500+4.9x700)/1200
>>>0002 A 25000 (3x10000+3.15x7000+3.2x8000)/25000[/color]
>>
>>awk 'NR == 1 { next } ## skip header
>>{
>> item[\$1,\$2] = \$1
>> dept[\$1,\$2] = \$2
>> qty[\$1,\$2] += \$3
>> tot[\$1,\$2] += \$3 * \$4}
>>
>>END {
>> for ( id in item )
>> printf "%s\t%s\t%d\t%s\n", item[id], dept[id], qty[id],
>> tot[id] / qty[id]
>>
>>}' "\$FILE" | sort
>>
>>--
>> Chris F.A. Johnson, author | <http://cfaj.freeshell.org>
>> Shell Scripting Recipes: | My code in this post, if any,
>> A Problem-Solution Approach | is released under the
>> 2005, Apress | GNU General Public Licence[/color]
>
>
> Thanks a lot, Chris! But now I'm seeing these errors when I test your
> code on my system ...
>
> \$ vi run.sh
> 1 #!/bin/sh
> 2
> 3 awk 'NR == 1 { next } ## skip header
> 4 {
> 5 item[\$1,\$2] = \$1
> 6 dept[\$1,\$2] = \$2
> 7 qty[\$1,\$2] += \$3
> 8 tot[\$1,\$2] += \$3 * \$4
> 9 }
> 10
> 11 END {
> 12 for ( id in item )
> 13 printf "%s\t%s\t%d\t%s\n", item[id], dept[id], qty[id],
> 14 tot[id] / qty[id]
> 15
> 16 }' "/var/tmp/test" | sort
>
> \$ ./run.sh
> awk: syntax error near line 3
> awk: illegal statement near line 3
> awk: syntax error near line 4
> awk: illegal statement near line 4
> awk: syntax error near line 5
> awk: illegal statement near line 5
> awk: syntax error near line 6
> awk: illegal statement near line 6
> awk: syntax error near line 12
> awk: illegal statement near line 12
>
> Not sure what's wrong with awk, maybe it's the square brackets?
>
> I should have mentioned that I'm using standard Bourne shell on
> Solaris, also tried bash ... same errors.
>[/color]

It's not your shell, it's your awk. You're using old, broken awk. Use
GNU awk (gawk), new awk (nawk), or /usr/xpg4/bin/awk on Solaris.

• 10-04-2007, 04:23 AM
unix
Re: shell script to consolidate inventory report
>[color=blue]
> It's not your shell, it's your awk. You're using old, broken awk. Use
> GNU awk (gawk), new awk (nawk), or /usr/xpg4/bin/awk on Solaris.[/color]

Bingo! Switching to nawk solved the problem. A million thanks to you
all!