12 Replies Latest reply: Jun 3, 2013 5:38 AM by user11196739 RSS

    shell script for report groupd on date

    user11196739
      Dear Team,
      I am try n gerrating a report by writing a shell script, the content of the report should be aggrigated based on date value exist in the content of the file. initially the file is zip format
      My file content is in below format
      031627787 034626800357002013050613310400000013500 CLT01 V1BTAG31627787 0000 AMK0
      031627787 0313557408002013050713341700000007000 CLT01 GRAZM231627787 0000 AMK0
      031627787 0316579457002013050613335900000067200 CLT01 GEIDOR31627787 0000 AMK0
      031627787 046329433121002013050713443800000024200 CLT01 KLAGSU31627787 0000 AMK0
      i am calculating number of calls based on date as where $PrevDay is the Previous date value
      calls=`gunzip -c -v $HOME/$path1/$profile/cdr/*$FileDate*.cdr.gz|awk -v date=$PrevDay\
           '{ if (substr($0,43,8)== date) summe=summe+1}END {print summe}'`
      File contains the date in the data of 2nd column, now i have to aggrigate above data based on date in 2nd column, menas 1 date calls should come in one line another date calls should come in another line. e.g
      calls for 20130506 should come in one row and calls for 201307 should come in 2nd row.

      Kindly suggest.
        • 1. Re: shell script for report groupd on date
          Tommyreynolds-Oracle
          Your data is:
          031627787 034626800357002013050613310400000013500 CLT01 V1BTAG31627787 0000 AMK0
          031627787 0313557408002013050713341700000007000 CLT01 GRAZM231627787 0000 AMK0
          031627787 0316579457002013050613335900000067200 CLT01 GEIDOR31627787 0000 AMK0
          031627787 046329433121002013050713443800000024200 CLT01 KLAGSU31627787 0000 AMK0
          ....^....|....^....|....^....|....^....|....^....|....^....|....^....|....^....|
          1234567890123456789012345678901234567890123456789012345678901234567890123456789
          And your script it:
          gunzip -c -v $HOME/$path1/$profile/cdr/*$FileDate*.cdr.gz       |
          awk -v date=$PrevDay '
              {
                  if( substr($0,43,8) == date ) {
                      summe = summe + 1
                  }
              }
              END {
                  print summe
              }
          '
          So, your data is not constant width; how can you expect to extract the content using column counts?

          Tell us more about your data format.
          • 2. Re: shell script for report groupd on date
            user11196739
            Dear Reynolds,

            Thanks for your reply, the data will come at specific location e.g date value comes at position 43 and of length 8. Other values also has some specific position to start.

            Edited by: user11196739 on May 26, 2013 9:15 PM
            • 3. Re: shell script for report groupd on date
              user11196739
              Please reply.
              • 4. Re: shell script for report groupd on date
                Catch~22
                Can you provide a valid example and show how the result should look like?
                • 5. Re: shell script for report groupd on date
                  user11196739
                  Thanks for Reply Dude,

                  Here am explaining it again
                  The file content is as below, all the values has a fixed index location in the file, in our case its call_date start with index location 43 and ends with 50.
                  File1
                  031627787 034626800357002013050613310400000013500 CLT01 V1BTAG31627787 0000 AMK0
                  031627787 0313557408002013050613341700000007000 CLT01 GRAZM231627787 0000 AMK0
                  031627787 0316579457002013050713335900000067200 CLT01 GEIDOR31627787 0000 AMK0
                  031627787 046329433121002013050713443800000024200 CLT01 KLAGSU31627787 0000 AMK0
                  i am calculating number of calls based on date as where $PrevDay is the Previous date value
                  calls=`gunzip -c -v $HOME/$path1/$profile/cdr/*$FileDate*.cdr.gz|awk -v date=$PrevDay\
                  '{ if (substr($0,43,8)== date) summe=summe+1}END {print summe}'`

                  calls are calculated based on number of occurnace of that date value in the file. if the report is generating on e.g 20130507, and if there are multiple date values in the file the report shuold be like as

                  "Data1,Data2,Data3,count of call_date(20130507),data4,data5
                  Data1,Data2,Data3,count of call_date(20130506),data4,data5" for file1 the same can be repeted for other files in loop.


                  Please let me know if more clarification is required.

                  Edited by: user11196739 on May 30, 2013 9:26 PM
                  • 6. Re: shell script for report groupd on date
                    Catch~22
                    The file content is as below, all the values has a fixed index location in the file, in our case its call_date start with index location 43 and ends with 50.
                    Your given example looks invalid and variable length that does not show a date at a fixed position. Certainly not starting at 43 - 50.
                    "Data1,Data2,Data3,count of call_date(20130507),data4,data5
                    Data1,Data2,Data3,count of call_date(20130506),data4,data5" for file1 the same can be repeted for other files in loop.
                    What is data1 - data5, supposed to represent?

                    Sorry, I do not understand your posting.
                    • 7. Re: shell script for report groupd on date
                      user11196739
                      Hi Dude,

                      let me try n explain again.

                      the data has a fixed index location, may be during the post the index location not matched but its sure the data of file has a fixed index location.

                      Data1,Data2,Data3,2,data4,data5 where 2 is the count for the date value 20130507
                      Data1,Data2,Data3,2,data4,data5" where 2 is the count for the date value 20130506 there could be other dates as well e.g
                      Data1,Data2,Data3,3,data4,data5" where 3 is the count for the date value 20130505 So now all the data of a file is grouped now based on date value exist in the file.

                      for file1 the same can be repeted for other files in loop.

                      Data1, and other are values calculated in script, only call date is from this file. I need to calculate the number of calls for a date from the file and append that value along with other values in the report as explained above format. Only the requirement is data in report should be grouped on call_date.

                      Edited by: user11196739 on May 30, 2013 11:04 PM

                      Edited by: user11196739 on May 30, 2013 11:50 PM
                      • 8. Re: shell script for report groupd on date
                        Catch~22
                        Please put your example between "code" tags as explained in the forum FAQ at the top of the page. Having a valid example of your input data can help to answer your question.

                        Sorry, I still have no idea what data1, 2, 3, 4, and 5 should represent. Perhaps it would be clear if you provide how the result should look like with actual data based on your given example.

                        If the data1 - 5 is not from the file or example it is irrelevant to answer the question, which then was only a matter to count the occurrences of a particular date pattern in the file. Is this correct?

                        Actually, to do this kind of job should be easier to do in SQL using a database.
                        • 9. Re: shell script for report groupd on date
                          Catch~22
                          Create following example:
                          $ cat sample.dat
                          12345678920130510abcde
                          12345678920130711xyz
                          12345678920130510abcde
                          12345678920130711xyz
                          12345678920130510abcde
                          12345678920130510abcde
                          12345678920130510abcde
                          12345678920130711xyz
                          12345678920130510abcde
                          12345678920130510abcde
                          12345678920130711xyz
                          12345678920130510abcde
                          Create following script:
                          #!/bin/bash
                          # File: sample
                          # Purpose: Demo 
                          
                          ifile=sample.dat
                          wfile=sample.tmp
                          ofile=sample.out
                          
                          # Extract position 10 - 17 of each line and write to a tempfile.
                          
                          while read line; do
                             date=${line:9:8}
                             echo "`fgrep -o $date $ifile | wc -l`, $date" >> $wfile
                          done < $ifile
                          
                          # Remove duplicate lines
                          
                          sort $wfile | uniq -d > $ofile
                          
                          # Cleanup
                          
                          rm $wfile
                          Test:
                          $ sh sample
                          
                          $ cat sample.out
                          4, 20130711
                          8, 20130510
                          The above method may not be the most efficient way, but is certainly easy to understand.

                          If you have Oracle database installed and need to this or similar tasks on a regular basis you might consider importing the data using sql*loader and then use SQL to create your reports.
                          • 10. Re: shell script for report groupd on date
                            user11196739
                            Yes correct Dude,

                            I just need to count the number of count of a date value in the file,
                            123456789012345678901234567890123456789012201305061234567890
                            123456789012345678901234567890123456789012201305061234567890
                            123456789012345678901234567890123456789012201305071234567890
                            123456789012345678901234567890123456789012201305071234567890
                            calls=`gunzip -c -v $HOME/$path1/$profile/cdr/*$FileDate*.cdr.gz|awk -v date=$PrevDay\
                            '{ if (substr($0,43,8)== date) summe=summe+1}END {print summe}'`
                            and place that count in report along with other values, initially my file is in .gz format. above command will calculate the count of particular date value but when it comes to multiple dates if fails need suggestion.
                            • 11. Re: shell script for report groupd on date
                              user11196739
                              Hi Dude,

                              Please reply.
                              • 12. Re: shell script for report groupd on date
                                user11196739
                                I am reformatting my input data for the file
                                031627787                   034626800357002013050613310400000013500                    CLT01                                            V1BTAG31627787                              0000                                        AMK0
                                031627787                     0313557408002013050613341700000007000                    CLT01                                            GRAZM231627787                              0000                                        AMK0
                                031627787                     0316579457002013050713335900000067200                    CLT01                                            GEIDOR31627787                              0000                                        AMK0
                                031627787                   046329433121002013050713443800000024200                    CLT01                                            KLAGSU31627787                              0000                                        AMK0
                                1234567890123456789012345678901234567890123456789012345678901234567890
                                                                                                         
                                This is the actual sample file to be read and calculate the count for the date values.

                                Edited by: user11196739 on Jun 3, 2013 3:37 AM