formatting and count in report

skd · #1 (**permalink**) 11-10-04, 15:15

I have a report from which i want to produce new report

here is format of old report
===================
/PA/WASHINGTON/
dbC has a total of 4156 records
File A05233415 has 2 error record(s) with 2 not worked
dbM has a total of 8272 records
/PA/WESTMORELAND/
dbC has a total of 33579 records
File A05233415 has 22 error record(s) with 9 not worked
dbM has a total of 11398 records
/SC/GREENVILLE/
dbC has a total of 877 records
File A06000231 has 1 error record(s) with 1 not worked
File A11060225 has 1 error record(s) with 0 not worked
dbM has a total of 12444 records
/SC/KERSHAW/
dbC has a total of 1479 records
dbM has a total of 2615 records

Desired new format
===============
PA/WASHINGTON dbC has a total of 4156 records
PA/WASHINGTON dbM has a total of 8272 records

PA/WESTMORELAND dbC has a total of 33579 records
PA/WESTMORELAND dbM has a total of 11398 records

PA dbC subtotal=37732(4156+33579)
PA dbM subtotal=19670(8272+11398)

SC/GREENVILLE dbC has a total of 877 records
SC/GREENVILLE dbM has a total of 12444 records

SC/KERSHAW dbC has a total of 1479 records
SC/KERSHAW dbM has a total of 2615 records

SC dbC subtotal=2356(877+1479)
SC dbM subtotal=15059(12444+2615)
------------------------------------
total dbC=sum of subtotals of dbC
total dbM=sum of subtotals of dbM

i am not very good in awk but i guess there might be short and easy solution in awk. please advise.

vgersh99 · #2 (**permalink**) 11-10-04, 16:04

can you assume that your 'states' come in sequential bunches?
For example, you CANNOT have this situation:

Quote:

/PA/WASHINGTON/
dbC has a total of 4156 records
File A05233415 has 2 error record(s) with 2 not worked
dbM has a total of 8272 records
/PA/WESTMORELAND/
dbC has a total of 33579 records
File A05233415 has 22 error record(s) with 9 not worked
dbM has a total of 11398 records
/PA/BOSTON/
dbC has a total of 4156 records
File A05233415 has 2 error record(s) with 2 not worked
dbM has a total of 8272 records

skd · #3 (**permalink**) 11-10-04, 16:17

Hello vgresh99,
thanks for reading my thread so quick.
I am not quite sure about your question.could you please explain more?

vgersh99 · #4 (**permalink**) 11-10-04, 16:30

ok, start with something like this.
This assumes all of your PAs come in one 'bunch', all of your SCs come in one bunch and so on for the other 'states'

nawk -f skd.awk myFile.txt

Code:

BEGIN {
  SEP_state="/"
}


$1 ~ ("^" SEP_state) {
   stateTown=$0
   split($0, stA, SEP_state)
   if ( FNR==1 )
      state=stA[2]
   else if ( stA[2] != state ) {
      for (i in arr) {
         printf("%s %s subtotal=%d\n", state, i, arr[i]);
         tot[i] += arr[i];
         delete arr[i];
      }
   }
   next;
}

$0 !~ /^File/ {
  print stateTown, $0
  arr[$1] += $(NF-1)
}

END {
  print "------------------------------------------------------";
  for (i in tot)
     printf("total %s=%d\n", i, tot[i])
}

vgersh99 · #5 (**permalink**) 11-10-04, 17:13

or better yet:

Code:

BEGIN {
  SEP_state="/"
}

$1 ~ ("^" SEP_state) {
   if ( FNR != 1 && stateTown != $0 )
      printf "\n"
   sub("^" SEP_state,"");sub(SEP_state "$","");
   stateTown=$0;
   split($0, stA, SEP_state)
   if ( FNR==1 )
      state=stA[1]
   else if ( stA[1] != state ) {
      for (i in arr) {
         printf("%s %s subtotal=%d\n", state, i, arr[i]);
         tot[i] += arr[i];
         delete arr[i];
      }
      printf "\n"
   }
   next;
}

$0 !~ /^File/ {
  print stateTown, $0
  arr[$1] += $(NF-1)
}

END {
  print "------------------------------------------------------";
  for (i in tot)
     printf("total %s=%d\n", i, tot[i])
}

skd · #6 (**permalink**) 11-15-04, 14:15

sorry for late in testing your code.
there seems a minor problem.
here is my testdata

vgersh99 · #7 (**permalink**) 11-15-04, 15:35

where is it?

skd · #8 (**permalink**) 11-15-04, 16:11

oops
i didn't do it right earlier. here is same

vgersh99 · #9 (**permalink**) 11-17-04, 15:43

sorry for the delay. try this version:

Code:

BEGIN {
  SEP_state="/"
}


$1 ~ ("^" SEP_state) {
   if ( FNR != 1 && stateTown != $0 )
      printf "\n"
   sub("^" SEP_state,"");sub(SEP_state "$","");
   stateTown=$0;
   split($0, stA, SEP_state)
   if ( FNR==1 )
      state=stA[1]
   else if ( stA[1] != state ) {
      for (i in arr) {
         printf("%s %s subtotal=%d\n", state, i, arr[i]);
         tot[i] += arr[i];
         delete arr[i];
      }
      printf "\n"
   }
   next;
}

$0 !~ /^[ ]*File/ && /database has a total of/ {
  print stateTown, $0
  arr[$1] += $(NF-1)
}

END {
  print "------------------------------------------------------";
  for (i in tot)
     printf("total %s=%d\n", i, tot[i])
}

skd · #10 (**permalink**) 11-17-04, 16:12

seems working fine
is it possible to print subtotal line only one time right before value of $state changes?

vgersh99 · #11 (**permalink**) 11-17-04, 16:29

Quote:

Originally Posted by skd

seems working fine
is it possible to print subtotal line only one time right before value of $state changes?

do you mean instead of having:

Quote:

SC dbC subtotal=2356
SC dbM subtotal=15059

to have:

Quote:

SC subtotal=17415(2356+15059)

??

skd · #12 (**permalink**) 11-18-04, 08:54

something like this.

AR/GREENE customer database has a total of 11 records
AR/GREENE street database has a total of 6 records

AR/WOODRUFF customer database has a total of 2 records
AR/WOODRUFF street database has a total of 2 records

AR street subtotal=48042 // Pefect here
AR customer subtotal=168433 // Pefect here

MO/ANDREW customer database has a total of 106 records
MO/ANDREW street database has a total of 83 records

AR street subtotal=83 // should NOT write this
AR customer subtotal=106 // should NOT write this

MO/AUDRAIN customer database has a total of 2376 records
MO/AUDRAIN street database has a total of 377 records

AR street subtotal=377 // should NOT write this
AR customer subtotal=2376 // should NOT write this

|
|
|
|
|

MO/MCDONALD customer database has a total of 77 records
MO/MCDONALD street database has a total of 46 records

AR street subtotal=46 //should write MO Street subtotal=
AR customer subtotal=77 //should write MO customer subtotal=

MS/MARION customer database has a total of 15 records
MS/MARION street database has a total of 5 records

also subtotal count is not coming correct when state changes from AR to MO and so on.

vgersh99 · #13 (**permalink**) 11-18-04, 09:51

sorry 'bout that - try this version:

Code:

BEGIN {
  SEP_state="/"
}


$1 ~ ("^" SEP_state) {
   if ( FNR != 1 && stateTown != $0 )
      printf "\n"
   sub("^" SEP_state,"");sub(SEP_state "$","");
   stateTown=$0;
   split($0, stA, SEP_state)
   if ( FNR==1 )
      state=stA[1]
   else if ( stA[1] != state ) {
      for (i in arr) {
         printf("%s %s subtotal=%d\n", state, i, arr[i]);
         tot[i] += arr[i];
         delete arr[i];
      }
      state = stA[1];
      printf "\n"
   }
   next;
}

$0 !~ /^[ ]*File/ && /database has a total of/ {
  print stateTown, $0
  arr[$1] += $(NF-1)
}

END {
    printf "\n"
    for (i in arr) {
       printf("%s %s subtotal=%d\n", state, i, arr[i]);
       tot[i] += arr[i];
       delete arr[i];
    }

  print "------------------------------------------------------";
  for (i in tot)
     printf("total %s=%d\n", i, tot[i])
}

Internet Services

Web Development

Digital Marketing

Consumer Tech