Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Posts
    71

    Question Unanswered: formatting and count in report

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    can you assume that your 'states' come in sequential bunches?
    For example, you CANNOT have this situation:
    /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
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  3. #3
    Join Date
    Sep 2003
    Posts
    71
    Hello vgresh99,
    thanks for reading my thread so quick.
    I am not quite sure about your question.could you please explain more?

  4. #4
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    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])
    }
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  5. #5
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    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])
    }
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  6. #6
    Join Date
    Sep 2003
    Posts
    71
    sorry for late in testing your code.
    there seems a minor problem.
    here is my testdata

  7. #7
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    where is it?
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  8. #8
    Join Date
    Sep 2003
    Posts
    71
    oops
    i didn't do it right earlier. here is same
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    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])
    }
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  10. #10
    Join Date
    Sep 2003
    Posts
    71
    seems working fine
    is it possible to print subtotal line only one time right before value of $state changes?

  11. #11
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    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:
    SC dbC subtotal=2356
    SC dbM subtotal=15059
    to have:
    SC subtotal=17415(2356+15059)
    ??
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

  12. #12
    Join Date
    Sep 2003
    Posts
    71
    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.

  13. #13
    Join Date
    Apr 2004
    Location
    Boston, MA
    Posts
    325
    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])
    }
    Last edited by vgersh99; 11-18-04 at 10:59.
    vlad
    +-----------------------+
    | #include <disclaimer.h> |
    +-----------------------+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •