Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: awk - printing new lines based of 2 dates
Top Forums Shell Programming and Scripting awk - printing new lines based of 2 dates Post 302968775 by Ads89 on Monday 14th of March 2016 10:28:51 AM
Old 03-14-2016
awk - printing new lines based of 2 dates
I have some test data that is seperated out into annual records, each record has a start date (COL7), an end date (COL8) and a maturity date (COL18) - What I need to do is ensure that there is one record to cover each year right up until Maturity date (COL18).

In the first group of the below data for example the start date and end dates for the final record are 2018-12-01 and 2019-11-30, however the maturity date isnt until 2020-11-30 so i would need an extra record to cover that time period i.e. 2019-12-01 to 2020-11-30 -- The outputted record we need to carry all the same field values of the last record, except for COL9 which would be defaulted to 0 and the start and end dates would change the reflect the year that it is covering

Input

Code:
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,5678,TEST,2,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2016-12-01,2017-11-30,210365.77,AAA,P,4095224.45,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30

Desired Output

Code:
 
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,5678,TEST,2,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2016-12-01,2017-11-30,210365.77,AAA,P,4095224.45,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2019-12-01,2020-11-30,0,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30

I could also have an instance for example, where there are missing records in between the test data, and again this gaps would need filling with a record, taking the values from the previous record and again, setting COL9 to 0 and the start and end dates would change the reflect the year that it is covering

Input

Code:
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2019-11-30

Desired output

Code:
 
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2016-12-01,2017-11-30,0,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2019-11-30

Note: COL2,COL3 and COL4 are the keys and esentially whilever these 3 values are the same, those records are grouped together and the above logic should be performed on each group of records

For example the below test data contains 3 'groups' of data

Code:
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,5678,TEST,2,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2016-12-01,2017-11-30,210365.77,AAA,P,4095224.45,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,9999,TEST,3,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,9999,TEST,3,AA,AAAA,2016-12-01,2017-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2018-10-01
C,9999,TEST,3,AA,AAAA,2017-12-01,2018-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2018-10-01

I have tried writting the above using a loop in linux, but only managed to get that working partially, however the partial solution I came up with is very slow and isnt performant on large numbers of records.

I was hoping that maybe someone could help me write some of the above logic using awk? My knowledge of awk is very limited so any help would be much appreciated.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Printing lines with specific awk NF

I have this files: ./frm/lf_mt1_cd.Ic_cell_template.attr ./die/addgen_tb_pumd.Ic_cell_template.attr ./min_m1_n.Ic_cell_template.attr When I use: awk -F\/ '{print NF}' Would result to: 3 3 2 I would like to list the files with 3 fields on it. Any Suggestions? (1 Reply)
Discussion started by: jehrome_rando
1 Replies

2. Shell Programming and Scripting

printing two lines in awk as two columns in excel

hi guys, i would like to print two lines from a file as two adjacent columns using excel using awk.. i have this so far: awk '{for(i=1; i<=NF; i++) {printf("%s\n",$i)}}' "$count".ttt > "$count".csv #this to print the first line from the .ttt file as rows of the first column in the .csv... (9 Replies)
Discussion started by: npatwardhan
9 Replies

3. Shell Programming and Scripting

Gawk / Awk Merge Lines based on Key

Hi Guys, After windows died on my netbook I installed Lubuntu and discovered Gawk about a month ago. After using Excel for 10+ years I'm amazed how quick and easily Gawk can process data but I'm stuck with a little problem merging data from multiple lines. I'm an SEO Consultant and provide... (9 Replies)
Discussion started by: Jamesfirst
9 Replies

4. Shell Programming and Scripting

sed/awk : how to delete lines based on IP pattern ?

Hi, I would like to delete lines in /etc/hosts on few workstations, basically I want to delete all the lines for a list of machines like this : for HOST in $(cat stations.lst |uniq) do # echo -n "$HOST" if ping -c 1 $HOST > /dev/null 2>&1 then HOSTNAME_val=`rsh $HOST "sed... (3 Replies)
Discussion started by: albator1932
3 Replies

5. Shell Programming and Scripting

Help With AWK Matching and Re-printing Lines

Hi All, I'm looking to use AWK to pattern match lines in XML file - Example patten for below sample would be /^<apple>/ The sample I wrote out is very basic compared to what I am actually working with but it will get me started I would like to keep the matched line(s) unchanged but have them... (4 Replies)
Discussion started by: rhoderidge
4 Replies

6. Shell Programming and Scripting

awk - printing nth field based on parameter

I have a need to print nth field based on the parameter passed. Suppose I have 3 fields in a file, passing 1 to the function should print 1st field and so on. I have attempted below function but this throws an error due to incorrect awk syntax. function calcmaxlen { FIELDMAXLEN=0 ... (5 Replies)
Discussion started by: krishmaths
5 Replies

7. UNIX for Dummies Questions & Answers

awk solution to duplicate lines based on column

Hi experts, I have a tab-delimited file with one column containing values separated by a comma. I wish to duplicate the entire line for every value in that comma-delimited field. For example: $cat file 4444 4444 4444 4444 9990 2222,7777 6666 2222 ... (3 Replies)
Discussion started by: torchij
3 Replies

8. Shell Programming and Scripting

UNIX awk pattern matching and printing lines

I have the below plain text file where i have some result, in order to mail that result in html table format I have written the below script and its working well. cat result.txt Page 2015-01-01 2000 Colors 2015-02-01 3000 Landing 2015-03-02 4000 #!/bin/sh LOG=/tmp/maillog.txt... (1 Reply)
Discussion started by: close2jay
1 Replies

9. Shell Programming and Scripting

awk join lines based on keyword

Hello , I will need your help once again. I have the following file: cat file02.txt PATTERN XXX.YYY.ZZZ. 500 ROW01 aaa. 300 XS 14 ROW 45 29 AS XD.FD. PATTERN 500 ZZYN002 ROW gdf gsste ALT 267 fhhfe.ddgdg. PATTERN ERE.MAY. 280 PATTERRNTH 5000 rt.rt. ROW SO a 678 PATTERN... (2 Replies)
Discussion started by: alex2005
2 Replies

10. Shell Programming and Scripting

awk to reformat lines based on condition

The awk below uses the tab-delimeted fileand reformats each line based on one of three conditions (rules). The 3 rules are for deletion (lines in blue), snv (line in red), and insertion (lines in green). I have included all possible combinations of lines from my actual data, which is very large.... (0 Replies)
Discussion started by: cmccabe
0 Replies
All times are GMT -4. The time now is 10:27 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy