Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Extract repetead values in date range

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Extract repetead values in date range
# 1  
Old 08-31-2017
Extract repetead values in date range
Gents,

Using the following values:
Code:
3687650458 08/29/2017 1.67581
3687650388 08/29/2017 1.67581
3687650330 08/29/2017 1.67581
3687650330 08/29/2017 1.67581
3687650330 08/28/2017 2.67581
3687650330 08/28/2017 2.67581
3687650330 08/27/2017 3.67581
3687650330 08/27/2017 3.67581
3687650330 08/26/2017 4.67581
3687650330 08/26/2017 4.67581
3687650330 08/25/2017 5.67581
3687650330 08/25/2017 5.67581
3687650330 08/24/2017 6.67581
3687650330 08/24/2017 6.67581
3687650330 08/23/2017 7.67581
3687649144 08/29/2017 1.67581
3687649144 08/29/2017 1.67581
3687649144 08/28/2017 2.67581
3687649144 08/28/2017 2.67581
3687649144 08/27/2017 3.67581
3687649144 08/27/2017 3.67581
3687649144 08/26/2017 4.67581
3687649144 08/26/2017 4.67581
3687649144 08/25/2017 5.67581
3687649144 08/25/2017 5.67581
3687649144 08/24/2017 6.67581
3687649144 08/24/2017 6.67581
3687649144 08/23/2017 7.67581
3686449774 08/25/2017 5.67581
3686449774 08/24/2017 6.67581
3686449774 08/24/2017 6.67581
3686449774 08/23/2017 7.67581
3685250156 08/29/2017 1.67581
3685250156 08/29/2017 1.67581
3685250156 08/28/2017 2.67581
3685250156 08/28/2017 2.67581
3685250156 08/27/2017 3.67581
3685250156 08/27/2017 3.67581
3685250156 08/26/2017 4.67581
3685250156 08/26/2017 4.67581
3685250156 08/25/2017 5.67581
3685250156 08/25/2017 5.67581
3685250156 08/24/2017 6.67581
3685250156 08/24/2017 6.67581
3685250156 08/23/2017 7.67581
3685250108 08/29/2017 1.67581
3685250108 08/29/2017 1.67581
3685250108 08/28/2017 2.67581
3685250108 08/28/2017 2.67581
3685250108 08/27/2017 3.67581
3685250108 08/27/2017 3.67581
3685250108 08/26/2017 4.67581

Desired output
Code:
3687650330 08/23/2017 08/29/2017 7
3687649144 08/23/2017 08/29/2017 7
3685250156 08/23/2017 08/29/2017 7
3685250108 08/26/2017 08/29/2017 4

The required data in output are the minimum and maximum date where the same value in column is repeated during this days.. the column 3 is the difference between the 2 dates.. .

As u notice I need only the values which are more than 3 days repeated and always keep only the last date in this case
Code:
08/29/2017

.

Example i don't extract the value below because the repeated values are not ending with date
Code:
08/29/2017

.
Code:
3686449774 08/23/2017 08/25/2017 3

Please help, thanks..
# 2  
Old 08-31-2017
Any attempts / ideas / thoughts from your side? You promised last time...
This User Gave Thanks to RudiC For This Post:
jiam912
# 3  
Old 08-31-2017
Hi RudiC

Using the following code i get close..

Code:
dt=`awk '{print $2}' tmp5 | sort -n -t"/" -k3 -k1 -k2 | tail -1`

awk     '               {D1=$1
                         D2=$2
             D3=$3
                        }
         !(D1 in MIN)   {MIN[D1]=D2
                         MAX[D1]=D2
             TIM[D1]=D3
                         next
                        }
          D2 < MIN[D1]  {MIN[D1]=D2}
          D2 > MAX[D1]  {MAX[D1]=D2}
          END           {for (m in MIN) print m, MIN[m], MAX[m], TIM[m]}
        ' tmp5 | awk '{if($3 ~ "'$dt'") print}'

output
Code:
3685250108 08/26/2017 08/29/2017 1.67581
3687650330 08/23/2017 08/29/2017 1.67581
3685250156 08/23/2017 08/29/2017 1.67581
3687650458 08/29/2017 08/29/2017 1.67581
3687650388 08/29/2017 08/29/2017 1.67581
3687649144 08/23/2017 08/29/2017 1.67581

only i need to get the correct value in diff between 2 days..

Please, help.. Tks
# 4  
Old 08-31-2017
Not sure I understand - what exactly is missing? And, how to get at it?
This User Gave Thanks to RudiC For This Post:
jiam912
# 5  
Old 08-31-2017
Hi RudiC

I will like to get the days diff between min and max date.

Code:
3685250108 08/26/2017 08/29/2017 4
3687650330 08/23/2017 08/29/2017 7
3685250156 08/23/2017 08/29/2017 7
3687650458 08/29/2017 08/29/2017 1
3687650388 08/29/2017 08/29/2017 1
3687649144 08/23/2017 08/29/2017 7

thanks in advance.
# 6  
Old 08-31-2017
I am missing YOUR approach to calculate that required difference in your (?) attempt... howsoever, try
Code:
awk     '
                {DV = substr ($2, 7) substr ($2, 1, 2) substr ($2, 4, 2)
                }
!($1 in MIN)    {MIN[$1] = DV
                 MAX[$1] = DV
                 MIS[$1] = $2
                 MAS[$1] = $2
                 next
                }
DV > MXDV       {MXDV = DV
                }
DV < MIN[$1]    {MIN[$1] = DV
                 MIS[$1] = $2
                }
DV > MAX[$1]    {MAX[$1] = DV
                 MAS[$1] = $2
                }

END             {for (m in MIN) if (MAX[m] == MXDV) print m, MIS[m], MAS[m], MAX[m] - MIN[m] + 1
                }
' file
3685250108 08/26/2017 08/29/2017 4
3687650330 08/23/2017 08/29/2017 7
3685250156 08/23/2017 08/29/2017 7
3687650458 08/29/2017 08/29/2017 1
3687650388 08/29/2017 08/29/2017 1
3687649144 08/23/2017 08/29/2017 7

This User Gave Thanks to RudiC For This Post:
jiam912
# 7  
Old 09-01-2017
Hello RudiC,

It works perfectly.

Thanks a lot
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Create range of values and count values.

Gents, It is possible to generate a range of values according to column 1 and count the total of rows in the range. example input 15.3 15.5 15.8 15.9 16.0 16.1 16.8 17.0 17.5 18.0 output desired 15.0 - 15.9 = 4 (10 Replies)
Discussion started by: jiam912
10 Replies

2. UNIX for Dummies Questions & Answers

Find the count of files by last created date based on the given date range

My unix version is IBM AIX Version 6.1 I tried google my requirement and found the below answer, find . -newermt “2012-06-15 08:13" ! -newermt “2012-06-15 18:20" But newer command is not working in AIX version 6.1 unix I have given my requirement below: Input: atr files: ... (1 Reply)
Discussion started by: yuvaa27
1 Replies

3. Shell Programming and Scripting

Convert Column Values to a Range of Values

I have a list of columns with values that I need to transform into a row containing the range of each column. For example: "Column A" 1 2 3 4 10 12 14 15 16 17 18 "Column B" 1 4 5 6 (4 Replies)
Discussion started by: newbio
4 Replies

4. Programming

grepping a range of values

I need to return all records in a file starting with a row that says TABLE: <tabl name> lists of hexadecimal records TABLE: <some table> TABLe is a key word in the file. I know the name of the table I want to start with. I do not know the name of the table that I will end with. I just... (4 Replies)
Discussion started by: guessingo
4 Replies

5. Shell Programming and Scripting

Awk extract a range of values

Hi Input 10 131 11 179 11 170 20 142 20 131 20 144 21 178 22 155 22 196 23 144 23 184 24 194 24 191 24 218 25 167 25 131 26 189 (6 Replies)
Discussion started by: genehunter
6 Replies

6. Shell Programming and Scripting

extract number range from a file

Hi Everyone, a.txt 1272904667;1272904737;1 1272904747;1272904819;1 1272904810;1272904857;1 1272904889;1272904926;1 1272905399;1272905406;1 1272905411;1272905422;1 if i want to get the record, when the a.txt 1st field is between 1272904749 and 1272905399, any simple way by using awk,... (1 Reply)
Discussion started by: jimmy_y
1 Replies

7. Shell Programming and Scripting

To Create range of values

Hi, I have a file with the below like values with integers only in sorted order (May or may not be in sequence) Eg: File1.txt ----------- 1 2 3 4 5 6 . . . . . 10000 My requirement here is to create a range of values out put to a temp k (4 Replies)
Discussion started by: shiva447
4 Replies

8. Shell Programming and Scripting

Perl code to extract data from the range of date

Hi All, I'm still a newbie in perl programming. I have a data below say in test.tmp The output in test.tmp will be the same data as above sample in test.tmp . So after i get all the 4th column data within the range of month and year i need, then i will use the foreach () code to execute... (1 Reply)
Discussion started by: miskin
1 Replies

9. Shell Programming and Scripting

Get date range between 2 date input

Hi Experts, I have files name report_20090416 report_20090417 report_20090418 report_20090420 report_20090421 I have 2 input from user From Date: 20090417 To Date: 20090420 and I need to grep only those line in between. Output should be report_20090417 report_20090418... (3 Replies)
Discussion started by: tanit
3 Replies

10. Shell Programming and Scripting

how to extract a range of lines from a file

I am reading a file that contains over 5000 lines and I want to assign it to a shell variable array (which has a restriction of 1024 rows). I had an idea that if I could grab 1000 record hunks of the file, and pipe the records out, that I could perform a loop until I got to the end and process 1000... (5 Replies)
Discussion started by: beilstwh
5 Replies
Login or Register to Ask a Question