awk script to check and throw error for multiplication result

as7951 · May 8, 2018, 7:50am

I need to multiply column1 and column3 data and need to compare it with column5. Need to check multiplication and Throw error if result is greater or less than column5 values, though difference of +/- 2 will be ok

Ex - if column1 has 2.4 and column3 has 3.5, it will be ok if column5 have value in range from row1(6.4 to 10.4), row2(248 to 252), row3(22634.8 to 22638.8) Only need to check multiplication result and Throw error. File has header too, but need to skip header while validating it.

Correct File

a|b|c|d|e
2.4||3.5||8.4
5||50||250
6.55||3456||22636.8
Correct file

a|b|c|d|e
2.4||3.5||7.4
5||50||251
6.55||3456||22635.8

Worng file :

a|b|c|d|e
2.4||3.5||6.3
5|50|247
6.55||3456||22634.4

Worng file

a|b|c|d|e
2.4||3.5||10.5
5||50||253
6.55||3456||22638.9

Below is code im trying :

This seems to be ok to check result greater than 2

if ($5 > $1 * $3 + 2 ) {print "Error 401: column1 and colmn3 does not match with column5,Field position 5, Linenumber:"NR,$0}

But below is not working to check if result is less than 2 and throwing error if result of multiplication is equal to $5

if ($5 < $1 * $3 + 2 ) {print "Error 402: column1 and colmn3 does not match with column5,Field position 5, Linenumber:"NR,$0}

rdrtx1 · May 8, 2018, 8:23am

Change the plus to a minus in the Error 402 check condition.

as7951 · May 8, 2018, 8:35am

Hi rdtrx1,
Have done, but not working.
please suggest

RudiC · May 8, 2018, 10:37am

WHAT and HOW "not working"? The better the info you post, the sooner and better results.

Try to paranthesize the arithmetic expression. Try like

awk '
                        {L = 0}
$5 > ($1 * $3 + 2)      {L = 1}
$5 < ($1 * $3 - 2)      {L = 2}
L                       {print "Error", L + 400, ": column1 and colmn3 does not match with column5,Field position 5, Linenumber:" NR, $0}
' FS="|" file

as7951 · May 9, 2018, 2:38am

Hi Rudic,

By using the below code getting below wrong error code output
As multiplication result of $1*$3 is correct in $5. So i should not supposed to get error.
but still it is showing output as pasted below.
Ideally i should get error message only when multiplication result of $1 andd $3 is not correct and have deviation of more than +/- 2 when compared with $5

#!/bin/bash
awk -F"|" -v OFS="|" 'NR>1
                        {L = 0}
$5 > ($1 * $3 + 2)      {L = 1}
$5 < ($1 * $3 - 2)      {L = 2}
L                       {print "Error", L + 400, ": column1 and colmn3 does not match with column5,Field position 5, Linenumber:" NR, $0}
' a1.txt > b1.txt

Wrong output

Error|401|: column1 and colmn3 does not match with column5,Field position 5, Linenumber:1|a|b|c|d|e
2.4||3.5||8.4
5||50||250
6.55||3456||22636.8

RudiC · May 9, 2018, 3:44am

It IS absolutely correct, as "a" * "c" + 2 is less than "e" .
You might want to exclude the header line?

as7951 · May 10, 2018, 8:02am

Hi Rudic,

For the below row, multiplication result of $1 * $3 is equal to $5, so i should not get an error message, but im getting with your code
Error should display only for the bad records rows with header in another file , those have multiplication result deviation of more than +/- 2.

a|b|c|d|e
2.4||3.5||8.4

Also i am using below code(.awk) to do the same thing with below input file

BEGIN { FS="|" }
NR>1 { print $0 "\tis " (abs($5-($1*$3)) > 2 ? "bad" : "good") }
function abs(val) { return (val<0 ? -val : val) }

Executing as awk -f tst.awk file

Input

a|b|c|d|e
2.4||3.5||6.6
5||50||249
6.55||3456||22635.7
2.4||3.5||6.3
5|50|247
6.55||3456||22634.4

could you please help.
So with the above code i want to print only bad records in another file along with header and row number and not the both good and bad records.
Expected output

a||b||e
2.4||3.5||6.3   is bad, line number 1
5|50|247        is bad, line number 2
6.55||3456||22634.4     is bad, line number 3

RavinderSingh13 · May 10, 2018, 8:17am

Hello as7951,

Could you please try following and let me know if this helps you.

awk -F"|" 'FNR==1{print $1,$2,$5;next} (($5-($1*$3))>=-2) && ((($1*$3)-$5)<=2){next} {print $0 " is bad, line number " FNR}' OFS="||"  Input_file

Adding a non-one liner form of solution too now.

awk -F"|" '
FNR==1                                    {   print $1,$2,$5;   next               }
(($5-($1*$3))>=-2) && ((($1*$3)-$5)<=2)   {   next                                 }
                                          {   print $0 " is bad, line number " FNR }
' OFS="||"    Input_file

Thanks,
R. Singh

as7951 · May 10, 2018, 8:24am

Hi RavinderSingh13,

Your code only returning below header

a||b||e

RavinderSingh13 · May 10, 2018, 8:35am

Hello as7951,

Could you please check once if your Input_file is having carriage characters by doing.

cat -v  Input_file

In case it is there then try removing them by doing as follows.

tr -d '\r' < Input_file  > temp_file  &&  mv temp_file  Input_file

Once they are removed then try my previous post's code and let us know then.

Thanks,
R. Singh

as7951 · May 10, 2018, 8:53am

Hi RavinderSingh13,

Your code is working, but not showing error for last 2 rows

How to handle if a file has rows in thousands or in lakhs

Input file

a|b|c|d|e
2.4||3.5||6.6
5||50||249
6.55||3456||22635.7
2.4||3.5||6.3
5||50||247
6.55||3456||22634.4
2.4||3.5||9.1
5||50||256
6.55||3456||22638.9

Getting below output

a|b|c|d|e
2.4||3.5||6.3 is bad, line number 5
5||50||247 is bad, line number 6
6.55||3456||22634.4 is bad, line number 7

RudiC · May 10, 2018, 8:57am

Difficult to believe; this is what I get:

Error 401 : column1 and colmn3 does not match with column5,Field position 5, Linenumber:1 a|b|c|d|e

Correct, isn't it?

Error should display only for the bad records rows with header in another file , those have multiplication result deviation of more than +/- 2.
a|b|c|d|e
2.4||3.5||8.4 
Also i am using below code(.awk) to do the same thing with below input file
BEGIN { FS="|" }
NR>1 { print $0 "\tis " (abs($5-($1*$3)) > 2 ? "bad" : "good") }
function abs(val) { return (val<0 ? -val : val) }
Executing as awk -f tst.awk file

Input
a|b|c|d|e
2.4||3.5||6.6
5||50||249
6.55||3456||22635.7
2.4||3.5||6.3
5|50|247
6.55||3456||22634.4
could you please help.
So with the above code i want to print only bad records in another file along with header and row number and not the both good and bad records.
Expected output
a||b||e
2.4||3.5||6.3   is bad, line number 1
5|50|247        is bad, line number 2
6.55||3456||22634.4     is bad, line number 3

Try

awk '
BEGIN   {FS="|"
        }
function abs(val)       {return (val<0 ? -val : val)
                        }
NR == 1
abs($5-$1*$3) > 2 &&
NR > 1  {print $0 "\tis bad"
        }
' file
a|b|c|d|e
2.4||3.5||6.3    is bad
5|50|247    is bad
6.55||3456||22634.4    is bad

RavinderSingh13 · May 10, 2018, 8:58am

as7951:

Hi RavinderSingh13,
Your code is working, but not working after 3 rows,
only showing output for 3 rows.
How to handle if a file has rows in thousands or in lakhs
Input file
a|b|c|d|e
2.4||3.5||6.6
5||50||249
6.55||3456||22635.7
2.4||3.5||6.3
5||50||247
6.55||3456||22634.4
2.4||3.5||9.1
5||50||256
6.55||3456||22638.9
Getting below output
a|b|c|d|e
2.4||3.5||6.3 is bad, line number 5
5||50||247 is bad, line number 6
6.55||3456||22634.4 is bad, line number 7

Hello as7951,

That's obvious because NONE of them I believe are satisfying the conditions(since their difference is in range of +2 to -2, please check and let us know on same. Kindly check once and get back to us.

NOTE: Also try to encourage people by using THANKS button at the left most corner for their efforts.

Thanks,
R. Singh

as7951 · May 10, 2018, 9:04am

Hi RavinderSingh13,

Now getting required output with below

awk -F"|" 'FNR==1{print $0;next}(($5-($1*$3))>=-2) && (($5-($1*$3))<=2){next} {print $0 " is bad, line number " FNR}' OFS="|" a1.txt