INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MODIFICATION of SUMMATION script

MODIFICATION of SUMMATION script

(OP)
hi guys,

i have a script that basically just sums up the values of 2 particular columns in a file, grouped by the columns specified as well

The script is quite long, but it's basically just repetitive.. just for each condition.

This script only accepts one type of input file.

#!/usr/bin/sh
###############################################################################
# #
# Description: #
# This Shell Script sums the 2 columns as grouped by the the columns #
# specified #
# #
###############################################################################
FILENAME=$1
DELIMITER=$2
FIRST_COL=$3
SECOND_COL=$4
SALESDATE_COL=$5
STOREID=$6
UPC=$7
GTIN=$8
PROMOID=$9
echo ""
echo ".:Summation Tool:."

for FILE in ${FILENAME}
do
gzip -t ${FILE} 2>/dev/null

if [ $? -eq 1 ];
then
comm=cat
else
comm=gzcat
fi

if [ $# -eq 5 ]; then
$comm $FILE | awk -v col=$FIRST_COL -v col2=$SECOND_COL -v col3=$SALESDATE_COL -v sourcefile=$FILE -F "$DELIMITER" '
{
if(NR!=1)
{
if($col3!="" && $col!="" && $col2!="")
{
salesdate[$col3] = $col3
v[$col3] += $col
d[$col3] += $col2
}
}
}
END{
printf("\n%s%s%s%s%s%s\n","sales_date","|","sum(POS_QTY)","|","sum(POS_AMT)|","<source_file>")
for (i in v)
{
if(salesdate[i]!=1)
{
printf("%s%s%d%s%10.4f%s%s\n",salesdate[i],"|",v[i],"|",d[i],"|",sourcefile)
}
}
}'

elif [ $# -eq 6 ]; then
$comm $FILE | awk -v col=$FIRST_COL -v col2=$SECOND_COL -v col3=$SALESDATE_COL -v col4=$STOREID -v sourcefile=$FILE -F "$DELIMITER" '
{
if(NR!=1)
{
if($col3!="" && $col!="" && $col2!="")
{
salesdate[$col3$col4] = $col3 "|" $col4
v[$col3$col4] += $col
d[$col3$col4] += $col2
}
}
}
END{
printf("\n%s%s%s%s%s%s\n","sales_date|cust_id","|","sum(POS_QTY)","|","sum(POS_AMT)|","<source_file> ")
for (i in v)
{
if(salesdate[i]!=1)
{
printf("%s%s%d%s%10.4f%s%s\n",salesdate[i],"|",v[i],"|",d[i],"|",sourcefile)
}
}
}'

.
. (same pattern for [ $# -eq 7 ] up to [ $# -eq 9 ]
.

elif [ $# -gt 10 ]; then
echo "Too many parameters passed. Please pass only the file name, delimiter, the number columns for POS_QTY and POS_AMT, and number of fields to GROUP BY."
elif [ $# -lt 5 ]; then
echo "Not enough parameters passed. Please pass the file name, delimiter, the number columns for POS_QTY and POS_AMT, and number of fields to GROUP BY."
else
echo "No parameters passed. Please pass the file name, delimiter, the number columns for POS_QTY and POS_AMT, and number of fields to GROUP BY."
fi
done


Usage of the script:

sh <script file name> <file or files> <delimiter> <column # to sum1> <column # to sum2> <column #s for grouping>

so basically sample execution would look like this

$ sh getsums.sh ft-gnct-3398-cd-2012-07-07-140112.txt.gz " " 22 23 1 2 4

.:Summation Tool:.

sales_date|cust_id|UPC|sum(POS_QTY)|sum(POS_AMT)|<source_file>
2012-07-05|PL_000000000034014|PL_2003007476012|75| 75.9200|ft-gnct-3398-cd-2012-07-07-140112.txt.gz
2012-07-03|PL_000000000034012|PL_2003000322606|19| 19.5000|ft-gnct-3398-cd-2012-07-07-140112.txt.gz
2012-07-01|PL_000000000034010|PL_2003005081201|38| 38.9800|ft-gnct-3398-cd-2012-07-07-140112.txt.gz


THE MODIFICATION NEEDED:
1. there is an additional input file (2nd file)
- first, there should be a code added to check whether there is a 2ND file
- if there is no 2nd file, just execute the first original script as is
- if there is a 2nd file
- first column needed from this file needs to be
stored to an array to be compared with UPC=$7 which is
coming from the first file
- all those values that match between UPC=$7 and first column
of 2nd file should be "excluded in summation"
(entire line for that match)
- SO, basically, the array from first column of 2nd file needs to
be looped to UPC=$7, to compare each line if there's a match
- those that will match will be stored to a separate new array
- this new array contains the list of values that will be
excluded during summation


if you guys can come up/suggest codes for this, that would be awesome.
i would really appreciate it.
i would also like suggestions, and guide me through the whole process of building it, im quite new to this stuff, so i basically don't know anything,
but i'm willing to learn.

i hope you can all teach me and help me solve this and come up with a
correctly modified script.

Thanks a lot guys! hope to hear from you guys soon.
i really need this script done as soon as possible.

Thanks a lot.

RE: MODIFICATION of SUMMATION script

The first thing I'd do is try and get rid of that horrible repetitive code; you should be able to handle the additional columns in just one version of the awk script.

Do you have some sample input data?

Also, where do you propose to include the second file name in the command line... I can see the command line syntax becoming very confusing.

Annihilannic
tgmlify - code syntax highlighting for your tek-tips posts

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close