Shell Programming and Scripting

Hi Experts,
Please bear with me, i need help
I am learning AWk and stuck up in one issue.
First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique.
Second point : For duplicate rows, need to make them as single row in output file.
Third point : For duplicate rows, want to put all value of column 2 other from input file in column 16 in format (IN272018000235^IN27201523963) in output file other than value which is stored(IN27201800023963) in column 2 in output file.

Ex - In output file, please see column 16 it should have value as IN272018000235^IN27201523963(taken from column2) only from first three rows(duplicate) .Here we have not taken 3rd value(IN27201800023963) in column 16 of output file from column 2 of input file bcz we have already stored in column 2(IN27201800023963) of output file.

Have record of thousand of line..so duplicate rows are not limited to three, there can be 40, 50 any number of duplicate rows. So accordingly need to have value in column 57.

Please let me know if still my query is not clear

Input File

Code:

a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p
    POS|IN27201800023963|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN272018000235|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN27201523963|2018-04-24||27AACCE5198E1ZJ||500|0||9|45|9|45|||
    POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||

Expected Output File

Code:

   a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p
    POS|IN27201800023963|2018-04-24||27AACCE5198E1ZJ||1500|0||9|135|9|135|||IN272018000235^IN27201523963|
    POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||

Below is code tried to sum up value but giving output as

Code:

awk 'BEGIN{ FS = OFS = "|" }
       NR > 1{
           if ($5 == f5) {
               $7 += f7; $9 += f9; $11 += f11; $13 += f13;
               $15 += f15; $16 = $16 f2"^"$2
           } else { print rec }
       }
       {
           rec = $0; f5 = $5; f7 = $7; f9 = $9;
           f11 = $11; f13 = $13; f15 = $15; f2 = $2
       }
       END{ print rec }' input.txt

Code output: Not correct because getting column 2 value "IN27201523963" also in column 16 instead it should be like "IN272018000235^IN27201800023963"
The value which is present in column13 should not be present in column16.

Code:

POS|IN27201523963|2018-04-24||27AACCE5198E1ZJ||1500|0|0|9|135|9|135||0|IN272018000235^IN27201523963
POS|IN27201800022938|2018-04-05||27AAJFH2012G1ZS||2|4||6||7|8|||

In what way is it 'not working'? Some output/errors would be useful to see.


Thanks, in advance,
Robin

Hi rbatte1,

The code is not complete and giving error "Unmatched '." .

There was an unmatched '.

Code:

awk 'BEGIN {FS=OFS="|"} FNR==1 {a[$1]+= (f4[key]+=$4;f6[key]+=$6;f7[key]+=$7;f8[key]+=$8;f10[key]+=$10;f11[key]+=$11;)}' input.txt > output.txt

Try it now?

I don't think it's worth trying, as there are quite some errors in that one-liner. Amongst others: the a[$1] assignment is missing (unless you mean the parenthesized part, which a) is an error, b) returns nothing), key is undefined, all the arithmetics is done for the first line ONLY, no output is printed.

Is this a homework assignment? Homework and coursework questions can only be posted in the Homework & Coursework forum under special homework rules.

Please review the rules, which you agreed to when you registered, if you have not already done so.

If you did not post homework, please explain the source of the data you have shown us and explain why you are trying to do this. The input you have shown us does not match the description you have provided, and the output you say you want from the sample input you provided cannot be derived from the instructions you have provided. If this is not homework, please also explain where you found the start of the awk script that you have shown us, and why you are unable to complete it. (Adding a missing single-quote as noted in the diagnostic you mentioned in post #3 in this thread should not be hard for most awk or shell script writers.)

If you did post homework in the main forums, please review the guidelines for posting homework and repost.

Hi Rudic,
I am confused and not able to complete the code for above query.
can you please help.

---------- Post updated at 10:59 PM ---------- Previous update was at 10:57 PM ----------

Hi Don,
it's not a home work assignment,
i am little confused here and not able to resolve above query, as it is seems complex to me
, i need your help in this.
I write multiple code but none is working.