1

I have a problem in which Input file has Effective date and End date records. First 6 fields are the keys (12345A). We need to update End date(18 position) based on effective(8 position) date of the next record with same key - 1 day. For the records with newest effective date, 9999-12-31 should remain in End date. Below are the input and output expectations. Could someone please help me in this. I am fetching the file A as table unload and this has to be send later to interfacing application which is expected the end date to be populated like this, we can not change end date on table itself thats why we are trying to change it on unload file.

There is no data separator/delimiter and yes length of keys will always remain same.

Input (FILEA):

12345A22021-01-259999-12-31
12345A12021-01-019999-12-31
12345B32021-02-159999-12-31
67899C12021-03-019999-12-31
67899D32021-05-249999-12-31
67899D22021-04-029999-12-31

Output (FILEB):

12345A22021-01-259999-12-31
12345A12021-01-012021-01-24
12345B32021-02-159999-12-31
67899C12021-03-019999-12-31
67899D32021-05-249999-12-31
67899D22021-04-022021-05-23
8
  • 1
    Why 2021-01-24 in line 2, while line 1 has 2021-01-25 as effective date? Same for line 6 (05-23 instead of 05-24)? Otherwise something like sed -E 'N;s/^(.{6})(.)(.{10})(.*\n\1.{11}).*/\1\2\3\4\3/;P;D' fileA > fileB would do the trick.
    – Philippos
    Sep 7 at 14:50
  • (1) As Philippos says, if you have a requirement to subtract one from the previous value, you should say so. (2) Is there a requirement to compare the effective dates on consecutive lines, or just the keys? … … … … … … … … … … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete. Sep 7 at 19:04
  • @Philippos - yes, the end date should be newest record efective date - 1 day Sep 8 at 10:13
  • @Philippos - I ran your command, its giving this error - sed: Not a recognized flag: E Sep 8 at 11:35
  • This means your sed version is quite old and does not support extended regular expressions. You can use standard regular expressions by adding a couple of backslashes: sed 'N;s/^\(.\{6\}\)\(.\)\(.\{10\}\)\(.*\n\1.\{11\}\).*/\1\2\3\4\3/;P;D' fileA > fileB, but you still need to decrement the date …
    – Philippos
    Sep 8 at 11:45
0

As long as the effective date of the preceding record with same ID shall be taken as end date, you can do it with standard sed and the N;P;D pattern like this:

sed '$!N;s/^\(.\{6\}\)\(.\)\(.\{10\}\)\(.*\n\1.\{11\}\).*/\1\2\3\4\3/;P;D'

But as soon as you need to change the date, you need a utility that understands a calendar like GNU date:

date -d "2021-09-08 yesterday" +"%Y-%m-%d"

will output 2021-09-07. I doubt many versions of date will do the trick that way. But with a current GNU date and GNU sed, this should work:

sed -nE 'G
  s/^(.{6})(.{11}).*\n\1.(.{10}).*/\1\2\3/p
  s/\n.*//p
  s/^(.{7})(.{10})/echo \1$(date -d "\2 yesterday" +"%Y-%m-%d")/e
  h' fileA > fileB

Let me try to explain:

  • We use option n to suppress default output and option E for extended regular expressions (just for readability)
  • G appends the hold space, where we are going to keep the previous line with adapted date (see below)
  • s/^(.{6})(.{11}).*\n\1.(.{10})/\1\2\3/p replaces the date of the current line with the adapted date from hold space, only if it has the same ID (thus the backreference \1) and prints it
  • s/\n.*//p simply removes the appended line if no replacement did take place and prints it
  • s/^(.{7})(.{10})/echo \1$(date -d "\2 yesterday" +"%Y-%m-%d")/e make use of GNU sed's execute flag to send the date to date utility to decrement it
  • h saves this changed line to hold space
2
  • 1
    sed: Not a recognized flag: E I believe, we are using a really old version. Unfortunately, we cant update it as its going to migration in coming months to Azure. Sep 8 at 14:52
  • @ShubhamSharma Does the date command work for you? Then we'll find another solution without `sed
    – Philippos
    Sep 9 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.