shell script to consolidate inventory report - Unix

This is a discussion on shell script to consolidate inventory report - Unix ; 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 ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: shell script to consolidate inventory report

  1. 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

    Thanks a lot in advance!


  2. Re: shell script to consolidate inventory report

    On 2007-05-03, nikonsports@gmail.com wrote:
    > 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



    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 |
    Shell Scripting Recipes: | My code in this post, if any,
    A Problem-Solution Approach | is released under the
    2005, Apress | GNU General Public Licence

  3. Re: shell script to consolidate inventory report

    On May 3, 1:54 pm, "Chris F.A. Johnson" wrote:
    > On 2007-05-03, nikonspo...@gmail.com wrote:
    > > 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

    >
    > 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 |
    > Shell Scripting Recipes: | My code in this post, if any,
    > A Problem-Solution Approach | is released under the
    > 2005, Apress | GNU General Public Licence


    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.


  4. Re: shell script to consolidate inventory report

    sdai wrote:
    > On May 3, 1:54 pm, "Chris F.A. Johnson" wrote:
    >
    >>On 2007-05-03, nikonspo...@gmail.com wrote:
    >>
    >>>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

    >>
    >>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 |
    >> Shell Scripting Recipes: | My code in this post, if any,
    >> A Problem-Solution Approach | is released under the
    >> 2005, Apress | GNU General Public Licence

    >
    >
    > 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.
    >


    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.


  5. Re: shell script to consolidate inventory report

    >
    > 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.


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


+ Reply to Thread