topic Re: command / script help please .. in Operating System - HP-UX

command / script help please ..

someone_4 — Wed, 03 Oct 2001 16:01:13 GMT

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

Re: command / script help please ..

harry d brown jr — Wed, 03 Oct 2001 16:06:36 GMT

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

Re: command / script help please ..

someone_4 — Wed, 03 Oct 2001 16:08:13 GMT

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

Richard

Re: command / script help please ..

harry d brown jr — Wed, 03 Oct 2001 16:10:30 GMT

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

Re: command / script help please ..

someone_4 — Wed, 03 Oct 2001 16:12:44 GMT

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.

Re: command / script help please ..

harry d brown jr — Wed, 03 Oct 2001 16:14:23 GMT

Sorry, I thought you were having rounding errors.

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

Re: command / script help please ..

someone_4 — Wed, 03 Oct 2001 16:19:55 GMT

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

richard

Re: command / script help please ..

Herve BRANGIER — Wed, 03 Oct 2001 16:48:12 GMT

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?

Re: command / script help please ..

harry d brown jr — Wed, 03 Oct 2001 16:50:33 GMT

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.

Re: command / script help please ..

Sachin Patel — Wed, 03 Oct 2001 16:53:46 GMT

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

Re: command / script help please ..

someone_4 — Wed, 03 Oct 2001 16:58:14 GMT

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.

Re: command / script help please ..

Curtis Larson_1 — Wed, 03 Oct 2001 17:00:58 GMT

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]);
}'

Re: command / script help please ..

James R. Ferguson — Wed, 03 Oct 2001 17:04:27 GMT

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

Re: command / script help please ..

Curtis Larson_1 — Wed, 03 Oct 2001 17:04:58 GMT

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.

Re: command / script help please ..

harry d brown jr — Wed, 03 Oct 2001 17:05:25 GMT

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

Re: command / script help please ..

James R. Ferguson — Wed, 03 Oct 2001 17:12:48 GMT

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

Re: command / script help please ..

someone_4 — Wed, 03 Oct 2001 17:15:51 GMT

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

Re: command / script help please ..

Deshpande Prashant — Wed, 03 Oct 2001 17:24:50 GMT

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.

Re: command / script help please ..

James R. Ferguson — Wed, 03 Oct 2001 17:25:01 GMT

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

Re: command / script help please ..

James R. Ferguson — Wed, 03 Oct 2001 17:33:58 GMT

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