Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

find values between values in two different fields

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting find values between values in two different fields
# 1  
Old 03-22-2011
Data find values between values in two different fields
Hi,

I need help to find values between two different fields based on $6 (NUM) AND $1 (CD), within the same ID. The result should show the values between the NUMs which will be extracted from within $3 and $2 in data.txt file below.

data.txt
Code:
  ex    139    142    Sc_1000004    ID 4
  CD    139    142    Sc_1000004    ID 4    Num1
  sta   139    140    Sc_1000004
  ex    143    144    Sc_1000004    ID 4
  CD    148    150    Sc_1000004    ID 4    Num2
  ex    153    156    Sc_1000004    ID 4
  CD    153    156    Sc_1000004    ID 4    Num3
  sto   156    158    Sc_1000004

  ex    160    163    Sc_1000005    ID 5
  CD    160    163    Sc_1000005    ID 5    Num1
  sta   160    161    Sc_1000005
  ex    167    170    Sc_1000005    ID 5
  CD    167    170    Sc_1000005    ID 5    Num2
  ex    175    205    Sc_1000005    ID 5
  CD    175    205    Sc_1000005    ID 5    Num3
  sto   205    207    Sc_1000005

  ex    212    221    Sc_1000006    ID 6
  CD    212    221    Sc_1000006    ID 6    Num2
  sto   212    215    Sc_1000006
  ex    224    227    Sc_1000006    ID 6
  CD    224    227    Sc_1000006    ID 6    Num1
  sta   227    229    Sc_1000006

  ex    243    248    Sc_1000007    ID 7
  CD    243    248    Sc_1000007    ID 7    Num1
  sta   243    243    Sc_1000007
  ex    251    257    Sc_1000007    ID 7
  CD    251    257    Sc_1000007    ID 7    Num2
  ex    261    263    Sc_1000007    ID 7
  CD    261    263    Sc_1000007    ID 7    Num3
  sto   263    265    Sc_1000007

  ex    275    288    Sc_1000008    ID 8
  CD    275    288    Sc_1000008    ID 8    Num1
  sta   275    277    Sc_1000008

i want to have output like this:-
Code:
  NewVal    143 - 147     ID 4
  NewVal    151 - 152     ID 4
  NewVal    164 - 166     ID 5       
  NewVal    170 - 174     ID 5
  NewVal    222 - 223     ID 6
  NewVal    249 - 250     ID 7
  NewVal    257 - 260     ID 7

in the above output, for eg., "143 - 147" are the CD values extracted between NUM 1 and NUM 2. While "151 - 152" are the CD values extracted between NUM 2 and NUM 3 for ID 4 in the input file (data.txt)... and so on..but if there is only 1 NUM (such as NUM1 for ID 8), which means that only 1 CD exist for that ID, then no NewVal will be extracted.

I have thousands of values that i need to extract from hundreds of files like this Smilie. Would appreciate your kind help or advise to do this in awk or sed. Thanks...
# 2  
Old 03-22-2011
Code:
awk '$1=="CD"{if(ID==$6&&$2>P+1) print "NewVal\t"P" - "$2-1"\tID "ID; ID=$6;P=$3+1 }' data.txt
NewVal  143 - 147       ID 4
NewVal  151 - 152       ID 4
NewVal  164 - 166       ID 5
NewVal  171 - 174       ID 5
NewVal  222 - 223       ID 6
NewVal  249 - 250       ID 7
NewVal  258 - 260       ID 7

This User Gave Thanks to Chubler_XL For This Post:
redse171
# 3  
Old 03-22-2011
Hi Chubler_XL,

It works great!! Thanks so much for kind help Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

2. Shell Programming and Scripting

How to get the values of multipledot(.) separated fields?

Hello, I have a file which has the following contents : thewall............0000000000200000 kmemfreelater......0000000000000000 kmemgcintvl........0000000000000002 kmeminuse..........00000000223411C0 allocated..........0000000029394000 bucket.......... @.F1000A02800C2158 The mentioned... (4 Replies)
Discussion started by: rahul2662
4 Replies

3. Shell Programming and Scripting

How to find the X highest values in a list depending on the values of another list with bash/awk?

Hi everyone, This is an exemple of inpout.txt file (a "," delimited text file which can be open as csv file): ID, Code, Value, Store SP|01, AABBCDE, 15, 3 SP|01, AABBCDE, 14, 2 SP|01, AABBCDF, 13, 2 SP|01, AABBCDE, 16, 3 SP|02, AABBCED, 15, 2 SP|01, AABBCDF, 12, 3 SP|01, AABBCDD,... (1 Reply)
Discussion started by: jeremy589
1 Replies

4. Shell Programming and Scripting

Need help in finding sum for values in 2 different fields

Hi there, I have 2 files in following format cat file_1 Storage Group Name: aaaa HBA UID SP Name SPPort ------- ------- ------ 0 21 Storage Group Name: bbbb HBA UID... (2 Replies)
Discussion started by: jpkumar10
2 Replies

5. Shell Programming and Scripting

Assigning fields values to different variables

Hi, I have this code: cat file.txt | awk -F, 'NR==1{print $6","$8","$10","$20","$21","$19}' > file.tmp VAR1=`cat file.tmp | cut -d "," -f1` VAR2=`cat file.tmp | cut -d "," -f2` VAR3=`cat file.tmp | cut -d "," -f3`; VAR4=`cat file.tmp | cut -d "," -f4`; VAR5=`cat... (1 Reply)
Discussion started by: Tr0cken
1 Replies

6. Shell Programming and Scripting

distinct values of all the fields

I am a beginner to scripting, please help me in this regard. How do I create a script that provides a count of distinct values of all the fields in the pipe delimited file ? I have 20 different files with multiple columns in each file. I needed to write a generic script where I give the number... (2 Replies)
Discussion started by: vukkusila
2 Replies

7. UNIX for Dummies Questions & Answers

distinct values of all the fields

I am a beginner to scripting, please help me in this regard. How do I create a script that provides a count of distinct values of all the fields in the pipe delimited file ? I have 20 different files with multiple columns in each file. I needed to write a generic script where I give the number... (1 Reply)
Discussion started by: vukkusila
1 Replies

8. Shell Programming and Scripting

plus values from two files differient fields.

Hi Everyone, I have two files: filea: Sun Jun 21 14:37:56 2009 1 2 3 Sun Jun 21 11:47:16 2009 2 3 4 fileb: Sun Jun 21 14:37:56 2009 1 2 3 Sun Jun 21 11:47:17 2009 33 44 55 The output is filec: Sun Jun 21... (4 Replies)
Discussion started by: jimmy_y
4 Replies
Login or Register to Ask a Question