command / script help please ..

[someone_4]

Hey everything .. I have an issue here where I have a text file that is formatted like this:
the fields are

CUSTOMER,DATE TIME FINISHED,DOLLAR AMOUNT

customer_1,2001-10-02 05:49:40,1.98,
customer_1,2001-10-02 05:49:31,1.65,
customer_1,2001-10-02 05:49:28,23.19,
customer_2,2001-10-02 05:48:01,3.12,
customer_2,2001-10-02 05:48:00,13.95,
customer_3,2001-10-02 05:38:48,227.52,
customer_3,2001-10-02 05:38:36,213.25,
customer_4,2001-10-02 05:44:17,22.99,
customer_4,2001-10-02 05:44:44,2.69,

And the the final output I want it to look like this for each customer:
CUSTOMER,DATE,TOTAL AMOUNT

customer_1,2001-10-02,26.76

what I am having problems with is getting the sun for each customer.
Any ideas?

Richard

[harry d brown jr]

What are you using to sum these records? (perl, awk, ksh, ...??)

[someone_4]

I am not using anything
I dont even know where to start to get the sum.

Richard

[harry d brown jr]

It could be a floating point to decimal rounding issue, or a variable that hasn't been initialized?

[someone_4]

Well the thing is how do I seperate each customer to get the totals for each customer?
There are more then 4 customer all with differnt but uniqe names.

[harry d brown jr]

Sorry, I thought you were having rounding errors.

awk, depending upon the data file size, would work just fine for this.

[someone_4]

so what would be the command string to get the sum total of the last field for each customer ?

richard

[Herve BRANGIER]

Hi Richard

You can use ksh to do that :

Read you file with :

while read line
do
# SOME COMMANDS
done < file.name

Now commands :

customer=`echo $line | cut -f1 -d',' | cut -f2 -d'_'`
amount=`echo $line | cut -f3 -d','`

And for sum you can use "bc" :

total=`echo "$total + $amount" | bc`

HTH

Herv?

[harry d brown jr · Accepted solution]

cat datafile|sed "s/ /,/g"|
awk ' BEGIN { FS=","; OFS=","; dollars = 0; cust = ""; dateof = ""; firsttime =
1 }
{
if ($1 == cust && $2 == dateof ) {
dollars += $4
} else {
if ( firsttime == 0 ) {
print cust, dateof, dollars
}
firsttime = 0
cust = $1
dateof = $2
dollars = $4
}
}
END { print cust, dateof, dollars }'


Note, that there is a "sed" that replaces the space between the date and time to a comma.

[Sachin Patel]

Hi Richard,
I don't know the full answer but
cat test | awk -F, '{s+=$3} END {print s}'

this will print total of 3rd field.

Sachin

[someone_4]

Hey Harry . here is what I am getting

syntax error The source line is 2.
The error context is
BEGIN { FS=","; OFS=","; dollars = 0; cust = ""; dateof = ""; f
irsttime = >>>
<<<
awk: The statement cannot be correctly parsed.
The source line is 2.

[Curtis Larson_1]

cat yourfile |
awk ' {
if ( NF < 2 ) next;
if ( NR == 1 ) {header=$0;next;}

cus=$1;
split($2,array,",");
amount=array[2];
bigA[id]=bigA[id] + amount;
} END {
print header "\n";
for ( i in bigA ) printf("%s,%f\n",i,bigA[i]);
}'

[James R. Ferguson]

Hi Richard:

Take the script, below, and call it "my.sh". Then, do this:

# /.my.sh ./myinput #...where "myinput" is your data file to process:

#!/usr/bin/sh
awk -F, '{if (TOG==0) {TOG=1;PREV=$1}};
{if ($1==PREV)
{T=T+$3}
else
{print T;PREV=$1;T=$3}}
END {print T}' $1
exit 0
#.end.

...based on your file, the output will be:

26.82
17.07
440.77
25.68

Regards!

...JRF...

[Curtis Larson_1]

oops

a little typo there. cus = id

change cus=$1 to id=$1
or
bigA[id] becomes bigA[cus]

looks like you need a bit of adjusting for your header also. but i'm sure you can deal with that.

[harry d brown jr]

Sorry, you need to paste the script into a file and execute it, otherwise you will need to use the "\" for line continuation.

[James R. Ferguson]

Hi (again) Richard:

I really meant to show (too) the name associated with the total, like this:

customer_1 26.82
customer_2 17.07
customer_3 440.77
customer_4 25.68

So, use this:

#!/usr/bin/sh
awk -F, '{if (TOG==0) {TOG=1;PREV=$1}};
{if ($1==PREV)
{T=T+$3}
else
{print PREV,T;PREV=$1;T=$3}}
END {print PREV,T}' $1
exit 0
#.end.

Regards!

...JRF...

[someone_4]

LOL
James you read my mind ..
I was fixing to ask you about that.
Now harry and James both of yours work great.
But I would like to know what your script is doing. I know it is doing the job great but as far as the inside of the script can you explain what the functions are doing?

Richard

[Deshpande Prashant]

Hi Richard
Try this
-----------
for i in `cat report.txt |grep -v "DATE" |awk -F"," '{print $1}' |sort -u`
{
echo "\nCustomer : " $i
for dollar in `cat report.txt |grep $i |awk -F"," '{sum+=$3} END {print sum}' `
{
echo "Sum of Dollars : $dollar "
}
----

Thanks.
Prashant.

[James R. Ferguson]

Hi Richard:

You go "back-to-basics". If it's the first time (based on TOG equal to 0) then preserve the first field (awk's $1) in the variable called PREV and set TOG to 1.

Then, test to see if the first field of the input record is the same as PREV. If true, add the value of the third field ($3) to a counter called "T". Otherwise, if false, then the name ($1) changed, so print the name and the (T)otal summation and set the (T)otal to the current value ($3 in the record being processed).

At the "END", simply flush the name field (PREV) and the current (T)otal.

Remember that outside of the tick marks surrounding 'awk', the "$1" is the file passed to the script -- as the first positional parameter.

Regards!

...JRF...

[James R. Ferguson]

Hi (once again) Richard:

Oh, I forgot to mention in my explanation of how the script works, that the '-F,' set the interfield separator for 'awk' to the comma character. It was clear from your sample data that that was the field delimiter you chose.

...JRF...

[Robin Wakefield]

Hi Richard,

This was uses associative arrays, making it relatively short (& you get the date):

sed 's/ [^,]*//' yourfile |
awk -F, '{array[$1","$2]+=$3}
END{for ( a in array ) print a","array[a]}' |
sort

customer_1,2001-10-02,26.82
customer_2,2001-10-02,17.07
customer_3,2001-10-02,440.77
customer_4,2001-10-02,25.68

The sed is used to simply strip out the time.

Rgds, Robin.