Conditionally adding a column of output 1

[SteveR77]

I know this should be easy but I am not seeing a clear answer to this.

I have a data file with 15 fields of data. Field 15 can contain data that is 0000000000_XX or XXQQQQQQ.

What I want to do is check the field for the underscore and return the last two characters if they exist otherwise I want the first two characters. The characters in question are a divisional code I will use later to group, sort and generate totals in a report.


Thank you in advance for your assistance and make it a great day.


SteveR77


I.T. where a world of choas and confusion comes down to nothing but 1s and 0s.

 

[LKBrwnDBA]

Try this:

#      V--- Set the field separator here
awk -F' ' '{if (index($1,"_")>0) print substr($15,length($15)-1); else print substr($1,1,2);}' InFile

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SteveR77]

Here is what I have but my results are off -

awk -F: '{ if($1 == 2014 && $2 == 9);if (index($15,"_")>0) print substr($15,length($15)-1);else print substr($1,1,2);print $2 $3 $1 $4 $5 $6 $8 $9 $10 $12 $13 $14 $15}' activity_log | sort -n -k3


I am not getting the substring or first condition executing to limiting the records which are returned.

I get records like this:

01152014 449052654293674hpoprNCBPFC1615000002_HP
01152014 44959262915440268hpoprCGHCFC08015000001_HP

When what I am attempting to get is this:

Field 15 = 2251000020_MS

090920141244022241026477863msoprNCFCFC12251000028_MSMS
0909201412590322438362250581msoprNCFCFC12251000020_MSMS


Thanks again for your assistance.

SteveR77

I.T. where a world of choas and confusion comes down to nothing but 1s and 0s.

 

[SteveR77]

CORRECTION - Field 15 has to be referenced for the substring.

awk -F: '{ if($1 == 2014 && $2 == 9) if (index($15,"_")>0) print substr($15,length($15)-1); else print substr($15,1,2);print $2 $3 $1 $4 $5 $6 $8 $9 $10 $12 $13 $14 $15}' activity_log | sort -n -k3

I.T. where a world of choas and confusion comes down to nothing but 1s and 0s.

 

[LKBrwnDBA]

It seems your data file has no character separator (not a delimited file type), therefore you need to provide us with the format like: field-n, start position, field length.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SteveR77]

LKBrwnDBA,

Apologies, but indirectly I did provide the data file's field seperator it is apart of the command line.

awk -F: '{ if($1 == 2014 && $2 == 9) if (index($15,"_")>0) print substr($15,length($15)-1); else print substr($15,1,2);print $2 $3 $1 $4 $5 $6 $8 $9 $10 $12 $13 $14 $15}' activity_log | sort -n -k3

The -F switch specifies the character to follow is the delimiter for the field speperator and is another option for how FS= can be defined.

However, the short answer is the field seperator is a colon.


Thanks again.

I.T. where a world of choas and confusion comes down to nothing but 1s and 0s.