[SOLVED] looping thru input files with different number of days.

btacuso · 11-03-2010, 12:40 AM

I want to itemize addition for each type of transactions. The example below is for 3 days,10/13/2010, 10/14/2010, & 10/15/2010.

PROD =89.25+89.25+104.47= 282.97
TAX =9.37+9.37+10.37 = 29.71
ETAX = 1+1+1 = 3
Total = 315.68

My problem is how do I make a loop so that the script can parse thru all the items on all dates if the input file have variable transaction days? What I mean is that it might be 1 day only, or
2 days, or 10 days. Please see attachment for easier to read file(notepad).

Date Trans ITEM_NO Debit Credit Balance
Oct13'10 PROD 328 89.25 89.25
Oct13'10 TAX 328 9.37 98.62
Oct13'10 ETAX 328 1.00 99.62
Oct14'10 PROD 328 89.25 188.87
Oct14'10 TAX 328 9.37 198.24
Oct14'10 ETAX 328 1.00 199.24
Oct15'10 PROD 328 104.47 303.71
Oct15'10 TAX 328 10.97 314.68
Oct15'10 ETAX 328 1.00 315.68
Oct16'10 TOTAL 328 315.68 CR 0.00

estabroo · 11-03-2010, 04:43 AM

depends on the language you use, for perl a while(<>) would work nicely

Code:

perl -e 'while(<>) { chomp; @info = split(/ /); push $data{$info[1]}, $info[-1]; }; $total = 0; print "$item = "; foreach $item (keys(%data)) { $ttot = 0; foreach $val (@{$data{$item}) { $ttot += $val; print "$val +"; } print " = $ttot\n"; $total += $ttot; } print "Total = $total\n";'

hmm guess that really shouldn't be a one-liner, note I didn't test that at all (too early in the morning)

btacuso · 11-03-2010, 10:19 AM

I would prefer a script in bash/awk where I have already a little background, but yes, I am also interested how it works in other languages. Thanks.

estabroo · 11-03-2010, 05:30 PM

Sorry I don't know awk well enough to help out, but here is that one liner corrected and working

Code:

perl -e 'while(<>) { chomp; @info = split(" ");  next if $info[1] \!~ /PROD|TAX|ETAX/; push @{$data{$info[1]}}, $info[-2] }; $total = 0 ; foreach $item ( keys ( %data ) ) { $ttot = 0; $i=0; $size = @{$data{$item}}-1; print "$item = "; foreach $val ( @{$data{$item}} ) { $ttot += $val ; print "$val"; print " + " if $i < $size; $i++; } print " = $ttot\n" ; $total += $ttot ; }  print "Total = $total\n" ; ' sample.txt

or spread out as a regular script

Code:

#!/usr/bin/perl
while(<>) { 
    chomp; 
    @info = split(" ");  
    next if $info[1] !~ /PROD|TAX|ETAX/; 
    push @{$data{$info[1]}}, $info[-2];
} 
 
$total = 0; 
foreach $item ( keys ( %data ) ) {
    $ttot = 0; 
    $i=0; 
    $size = @{$data{$item}}-1; 
    print "$item = "; 
    foreach $val ( @{$data{$item}} ) { 
        $ttot += $val; 
        print "$val"; 
        print " + " if $i < $size; 
        $i++; 
    } 
    print " = $ttot\n";
    $total += $ttot; 
}  
print "Total = $total\n";

crts · 11-03-2010, 06:10 PM

Hi,

if I understand correctly, then you just want the total of the fields PROD, TAX and ETAX, right? It does not really matter how many days there are. So you just need to add the numbers of the according field. Here is an awk example that will do it for the TAX field:

Code:

awk '/\<TAX\>/{tax+=$4}END{print tax}' sample.txt

With small changes this can be extended to sum up the other fields, too.

btacuso · 11-03-2010, 08:28 PM

Quote:

Originally Posted by estabroo

Sorry I don't know awk well enough to help out, but here is that one liner corrected and working

Code:

perl -e 'while(<>) { chomp; @info = split(" ");  next if $info[1] \!~ /PROD|TAX|ETAX/; push @{$data{$info[1]}}, $info[-2] }; $total = 0 ; foreach $item ( keys ( %data ) ) { $ttot = 0; $i=0; $size = @{$data{$item}}-1; print "$item = "; foreach $val ( @{$data{$item}} ) { $ttot += $val ; print "$val"; print " + " if $i < $size; $i++; } print " = $ttot\n" ; $total += $ttot ; }  print "Total = $total\n" ; ' sample.txt

or spread out as a regular script

Code:

#!/usr/bin/perl
while(<>) { 
    chomp; 
    @info = split(" ");  
    next if $info[1] !~ /PROD|TAX|ETAX/; 
    push @{$data{$info[1]}}, $info[-2];
} 
 
$total = 0; 
foreach $item ( keys ( %data ) ) {
    $ttot = 0; 
    $i=0; 
    $size = @{$data{$item}}-1; 
    print "$item = "; 
    foreach $val ( @{$data{$item}} ) { 
        $ttot += $val; 
        print "$val"; 
        print " + " if $i < $size; 
        $i++; 
    } 
    print " = $ttot\n";
    $total += $ttot; 
}  
print "Total = $total\n";

Thanks. I will keep this as a reference if I switch to perl.

btacuso · 11-03-2010, 08:33 PM

Quote:

Originally Posted by crts

Hi,

if I understand correctly, then you just want the total of the fields PROD, TAX and ETAX, right? It does not really matter how many days there are. So you just need to add the numbers of the according field. Here is an awk example that will do it for the TAX field:

Code:

awk '/\<TAX\>/{tax+=$4}END{print tax}' sample.txt

With small changes this can be extended to sum up the other fields, too.

Yes, I want those plus the sum total. I might receive a file which has a day or more transactions. I will try to figure out how to make a loop out of your sample. Thanks.

crts · 11-03-2010, 10:22 PM

Quote:

Originally Posted by btacuso

Yes, I want those plus the sum total. I might receive a file which has a day or more transactions. I will try to figure out how to make a loop out of your sample. Thanks.

The point is that you do not need a loop. awk processes every line of the file. If it finds the search pattern in a line then field number for is added to the variable tax. It does not matter how many days there are. Simply add the other patterns (ETAX, PROD) and add to variables etax and prod when the corresponding pattern is encountered. In the END block you can then still calculate the sum total like
print "total: "prod+tax+etax

Just try it and observe the output.

btacuso · 11-03-2010, 11:54 PM

Quote:

Originally Posted by crts

The point is that you do not need a loop. awk processes every line of the file. If it finds the search pattern in a line then field number for is added to the variable tax. It does not matter how many days there are. Simply add the other patterns (ETAX, PROD) and add to variables etax and prod when the corresponding pattern is encountered. In the END block you can then still calculate the sum total like
print "total: "prod+tax+etax

Just try it and observe the output.

It worked. Now I can close this thread. Thanks again.