This discussion is archived
12 Replies Latest reply: Jun 3, 2013 3:38 AM by user11196739 RSS

shell script for report groupd on date

user11196739 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Please reply.
  • 4. Re: shell script for report groupd on date
    Dude! Guru
    Currently Being Moderated
    Can you provide a valid example and show how the result should look like?
  • 5. Re: shell script for report groupd on date
    user11196739 Newbie
    Currently Being Moderated
    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
    Dude! Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Dude! Guru
    Currently Being Moderated
    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
    Dude! Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Dude,

    Please reply.
  • 12. Re: shell script for report groupd on date
    user11196739 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points