awk if value between x and y

SDohmen · March 28, 2017, 5:35am

Hello,

I am trying to create a awk (prefered) script that prints a extra value based on a number in one of the other columns.

awk  '
BEGIN           {FS = OFS = ";"
                 print "price"
                }
                { if ($1 <= 0.99 && $1 >= 0.01)
                        print $0,"6";
                  elseif ($1 <= 1.99 && $1 >= 1)
                        print $0,"5";
                  elseif ($13 <= 2.99 && $13 >= 2)
                        print $0,"3";
                }
' $1 > $2

There are more lines to add but this should suffice for the testing i am doing atm.

The problem is that the code above does not seem to work.

The result i am getting is as follows:

0.50    3
1.21    5
2         3
2         5
2         3
2.01     5
2.01     3
2.01     5
2.99     3
2.99     5
2.99     3
3         5
3         3
3         5

the result should be as below:

0.50    6
1.21    5
2         3
2         3
2         3
2.01     3
2.01     3
2.01     3
2.99     3
2.99     3
2.99     3
3         
3         
3

The last couple lines should be empty as i dont have a value set for those yet.

Can someone tell me or show me what i have wrong on this?

Scrutinizer · March 28, 2017, 5:45am

Try:

$1 instead of $13
else if instead of elseif
create an extra condition for else to print lines that do not match the other criteria, or use a variable.

SDohmen · March 28, 2017, 5:58am

Oops i had the mistype for 13 there.

I tried the 2 other lines and so far it looks working. I will add some more to see if its 100% but it looks promising for which i am gratefull.

RudiC · March 28, 2017, 7:22am

How about

awk  '
BEGIN   {split ("6 5 3", B)
        }
        {print $0, B[int($1)+1]
        }
' OFS="\t" file
0.50    6
1.21    5
2    3
2    3
2    3
2.01    3
2.01    3
2.01    3
2.99    3
2.99    3
2.99    3
3    
3    
3

(admittedly needs some adjustments for 0 and negative values of $1)

SDohmen · March 28, 2017, 8:49am

rudic:

How about

awk  '
BEGIN   {split ("6 5 3", B)
   }
   {print $0, B[int($1)+1]
   }
' OFS="\t" file
0.50    6
1.21    5
2    3
2    3
2    3
2.01    3
2.01    3
2.01    3
2.99    3
2.99    3
2.99    3
3    
3    
3

(admittedly needs some adjustments for 0 and negative values of $1)

Interesting line but i am not sure how it would work. The value 0 and negative cannot happen since they are filtered out already. The other thing is that the values arent in order and the full list of numbers is alot bigger.

Below is the current version which works with the help from Scrutinizer. The $14 value is the original position of the value so i did not change that in this one.

awk  '
BEGIN           {FS = OFS = ";"
                 print "Reference;EAN;Condition;Stock;Price;Deliverycode;;manufacturer;Titel;MPN;supplier_code;Sup Category;Category 2;Cost;Marge;DSCost;Vast Comm;Vari Comm;;;;Marge Berekend;CMD;CMD + Comm"
                }
                { if ($14 <= 0.99 && $14 >= 0.01)
                        print $0,"6";

                  else if ($14 <= 1.99 && $14 >= 1)
                        print $0,"5";

                  else if ($14 <= 2.99 && $14 >= 2)
                        print $0,"3";

                  else if ($14 <= 3.99 && $14 >= 3)
                        print $0,"2.5";

                  else if ($14 <= 4.99 && $14 >= 4)
                        print $0,"2";

                  else if ($14 <= 5.99 && $14 >= 5)
                        print $0,"2";

                  else if ($14 <= 6.99 && $14 >= 6)
                        print $0,"0.85";

                  else if ($14 <= 7.99 && $14 >= 7)
                        print $0,"0.7";

                  else if ($14 <= 8.99 && $14 >= 8)
                        print $0,"0.65";

                  else if ($14 <= 9.99 && $14 >= 9)
                        print $0,"0.55";

                  else if ($14 <= 10.99 && $14 >= 10)
                        print $0,"0.5";

                  else if ($14 <= 11.99 && $14 >= 11)
                        print $0,"0.45";

                  else if ($14 <= 12.99 && $14 >= 12)
                        print $0,"0.41";

                  else if ($14 <= 13.99 && $14 >= 13)
                        print $0,"0.38";

                  else if ($14 <= 14.99 && $14 >= 14)
                        print $0,"0.35";

                  else if ($14 <= 15.99 && $14 >= 15)
                        print $0,"0.33";

                  else if ($14 <= 16.99 && $14 >= 16)
                        print $0,"0.31";

                  else if ($14 <= 17.99 && $14 >= 17)
                        print $0,"0.29";

                  else if ($14 <= 18.99 && $14 >= 18)
                        print $0,"0.28";

                  else if ($14 <= 19.99 && $14 >= 19)
                        print $0,"0.26";

                  else if ($14 <= 24.99 && $14 >= 20)
                        print $0,"0.25";

                  else if ($14 <= 29.99 && $14 >= 25)
                        print $0,"0.24";

                  else if ($14 <= 34.99 && $14 >= 30)
                        print $0,"0.17";

                  else if ($14 <= 39.99 && $14 >= 35)
                        print $0,"0.15";

                  else if ($14 <= 44.99 && $14 >= 40)
                        print $0,"0.13";

                  else if ($14 <= 49.99 && $14 >= 45)
                        print $0,"0.12";

                  else if ($14 <= 54.99 && $14 >= 50)
                        print $0,"0.1";

                  else if ($14 <= 59.99 && $14 >= 55)
                        print $0,"0.08";
                  else if ($14 <= 64.99 && $14 >= 60)
                        print $0,"0.08";
                  else if ($14 <= 69.99 && $14 >= 65)
                        print $0,"0.08";
                  else if ($14 <= 74.99 && $14 >= 70)
                        print $0,"0.08";
                  else if ($14 <= 79.99 && $14 >= 75)
                        print $0,"0.08";
                  else if ($14 <= 84.99 && $14 >= 80)
                        print $0,"0.08";
                  else if ($14 <= 89.99 && $14 >= 85)
                        print $0,"0.08";
                  else if ($14 <= 94.99 && $14 >= 90)
                        print $0,"0.08";
                  else if ($14 <= 99.99 && $14 >= 95)
                        print $0,"0.08";

                  else if ($14 <= 109.99 && $14 >= 100)
                        print $0,"0.07";
                  else if ($14 <= 119.99 && $14 >= 110)
                        print $0,"0.07";
                  else if ($14 <= 129.99 && $14 >= 120)
                        print $0,"0.07";
                  else if ($14 <= 139.99 && $14 >= 130)
                        print $0,"0.07";
                  else if ($14 <= 149.99 && $14 >= 140)
                        print $0,"0.07";
                  else if ($14 <= 159.99 && $14 >= 150)
                        print $0,"0.07";
                  else if ($14 <= 169.99 && $14 >= 160)
                        print $0,"0.07";
                  else if ($14 <= 179.99 && $14 >= 170)
                        print $0,"0.07";
                  else if ($14 <= 189.99 && $14 >= 180)
                        print $0,"0.07";
                  else if ($14 <= 199.99 && $14 >= 190)
                        print $0,"0.07";
                  else if ($14 <= 209.99 && $14 >= 200)
                        print $0,"0.07";
                  else if ($14 <= 219.99 && $14 >= 210)
                        print $0,"0.07";
                  else if ($14 <= 229.99 && $14 >= 220)
                        print $0,"0.07";
                  else if ($14 <= 239.99 && $14 >= 230)
                        print $0,"0.07";
                  else if ($14 <= 249.99 && $14 >= 240)
                        print $0,"0.07";

                  else if ($14 <= 299.99 && $14 >= 250)
                        print $0,"0.06";
                  else if ($14 <= 349.99 && $14 >= 300)
                        print $0,"0.06";
                  else if ($14 <= 399.99 && $14 >= 350)
                        print $0,"0.06";
                  else if ($14 <= 449.99 && $14 >= 400)
                        print $0,"0.06";
                  else if ($14 <= 499.99 && $14 >= 450)
                        print $0,"0.06";
                  else if ($14 <= 549.99 && $14 >= 500)
                        print $0,"0.06";
                  else if ($14 <= 599.99 && $14 >= 550)
                        print $0,"0.06";
                  else if ($14 <= 649.99 && $14 >= 600)
                        print $0,"0.06";
                  else if ($14 <= 699.99 && $14 >= 650)
                        print $0,"0.06";
                  else if ($14 <= 749.99 && $14 >= 700)
                        print $0,"0.06";
                  else if ($14 <= 799.99 && $14 >= 750)
                        print $0,"0.06";
                  #else if ($14 <= 849.99 && $14 >= 800)
                  #      print $0,"0.06";
                  #else if ($14 <= 899.99 && $14 >= 850)
                  #      print $0,"0.06";
                  #else if ($14 <= 949.99 && $14 >= 900)
                  #      print $0,"0.06";
                  #else if ($14 <= 999.99 && $14 >= 950)
                  #      print $0,"0.06";
                  #else if ($14 <= 9999.99 && $14 >= 1000)
                  #      print $0,"0.06";

                  else
                        print;
                }

' $1 > $2

As you see it are alot of values to list and the last several lines even gave me errors which i cannot seem to find (Not really important as 750 is the highest number). If there is a way to shorten this that would be really nice.

RudiC · March 28, 2017, 12:21pm

Wouldn't

.
.
.
TMP = 0.06
if ($14 < 250) TMP = 0.07
if ($14 < 100) TMP = 0.08
if ($14 <  55) TMP = 0.1
if ($14 <  50) TMP = 0.12
if ($14 <  45) TMP = 0.13
if ($14 <  40) TMP = 0.15
if ($14 <  35) TMP = 0.17
if ($14 <  30) TMP = 0.24
if ($14 <  25) TMP = 0.25
if ($14 <  20) TMP = 0.26
if ($14 <  19) TMP = 0.28
if ($14 <  18) TMP = 0.29
if ($14 <  17) TMP = 0.31
if ($14 <  16) TMP = 0.33
if ($14 <  15) TMP = 0.35
if ($14 <  14) TMP = 0.38
if ($14 <  13) TMP = 0.41
if ($14 <  12) TMP = 0.45
if ($14 <  11) TMP = 0.5
if ($14 <  10) TMP = 0.55
if ($14 <   9) TMP = 0.65
if ($14 <   8) TMP = 0.7
if ($14 <   7) TMP = 0.85
if ($14 <   6) TMP = 2
if ($14 <   4) TMP = 2.5
if ($14 <   3) TMP = 3
if ($14 <   2) TMP = 5
if ($14 <   1) TMP = 6
print $0, TMP
.
.
.

yield the same result?

SDohmen · March 28, 2017, 12:26pm

That is actually also a very interesting way of working with the values. It even means the script could be 1/3 of the size.

I will test this out tomorrow when i have some time.

Thanks again for all the help.

Scrutinizer · March 28, 2017, 12:51pm

Another option, is to separate data and logic:

awk '
  NR==FNR {
    L[$1]=$2
    R[$1]=$3
    next
  }
  {
    m=""
    for (i in L)
      if ( $1 >= L && $1 <= R ) {
        m=i
        break
      }
    print $0, m
  }
' minmax data.txt

Command line version:

awk 'NR==FNR{L[$1]=$2; R[$1]=$3; next} {m=""; for(i in L) if($1>=L && $1<=R){m=i; break} print $0, m}'  minmax data.txt

$ cat minmax
6	0.01	0.99
5	1	1.99
3	2	2.99
2.5	3	3.99
2	4	4.99
2	5	5.99
0.85	6	6.99
0.7	7	7.99
0.65	8	8.99
0.55	9	9.99
0.5	10	10.99
0.45	11	11.99
0.41	12	12.99
0.38	13	13.99
0.35	14	14.99
0.33	15	15.99
0.31	16	16.99
0.29	17	17.99
0.28	18	18.99
0.26	19	19.99
0.25	20	24.99
0.24	25	29.99
0.17	30	34.99
0.15	35	39.99
0.13	40	44.99
0.12	45	49.99
0.1	50	54.99
0.08	55	59.99
0.07	100	109.99
0.06	250	299.99

$ cat data.txt
0.50
1.21
2
2
2
2.01
2.01
2.01
2.99
2.99
2.99
3
3
3

RudiC · March 28, 2017, 12:55pm

Or maybe

awk '
BEGIN   {m = split ("250 100 55 50 45 40 35 30 25 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 4 3 2 1 0", CV) 
         n = split ("0.06 0.07 0.08 0.1 0.12 0.13 0.15 0.17 0.24 0.25 0.26 0.28 0.29 0.31 0.33 0.35 0.38 0.41 0.45 0.5 0.55 0.65 0.7 0.85 2 2.5 3 5 6", SV)
        }
        {for (i=1; $14<CV && i<m; i++);
         print $14, SV
        } 
' OFS="\t" file

How about testing this and coming back with the result?

SDohmen · March 28, 2017, 1:00pm

Wow, both are awesome. I will test them all 3 and see which is easiest to work with for me. The last one looks very nice but we have to be carefull that there are a exact same number on each line so that makes it dangerous also when editing.

Again i will check them out and update this thread.

RudiC · March 28, 2017, 1:06pm

Intersperse some spaces, and / or use commas as delimiters

BEGIN    {m = split (" 250  100   55  50   45   40   35   30   25   20   19   18   17   16   15   14   13   12   11  10    9    8   7    6 4   3 2 1 0", CV) 
          n = split ("0.06 0.07 0.08 0.1 0.12 0.13 0.15 0.17 0.24 0.25 0.26 0.28 0.29 0.31 0.33 0.35 0.38 0.41 0.45 0.5 0.55 0.65 0.7 0.85 2 2.5 3 5 6", SV)

SDohmen · March 29, 2017, 10:48am

I will test them out as soon as i can but i did not get the time for it today. Maybe tonight i will have a couple min to test them. I will get back to this and update the thread asap.

SDohmen · April 3, 2017, 10:54am

Just as a quick update.

I tested all the ones that you guys posted for which again i am gratefull.

We decided to go with the first one from RudiC because that is the easiest to change if needed.

                {TMP = 0.06
                if ($14 < 250) TMP = 0.07
                if ($14 < 100) TMP = 0.08
                if ($14 <  55) TMP = 0.1
                if ($14 <  50) TMP = 0.12
                if ($14 <  45) TMP = 0.13
                if ($14 <  40) TMP = 0.15
                if ($14 <  35) TMP = 0.17
                if ($14 <  30) TMP = 0.24
                if ($14 <  25) TMP = 0.25
                if ($14 <  20) TMP = 0.26
                if ($14 <  19) TMP = 0.28
                if ($14 <  18) TMP = 0.29
                if ($14 <  17) TMP = 0.31
                if ($14 <  16) TMP = 0.33
                if ($14 <  15) TMP = 0.35
                if ($14 <  14) TMP = 0.38
                if ($14 <  13) TMP = 0.41
                if ($14 <  12) TMP = 0.45
                if ($14 <  11) TMP = 0.5
                if ($14 <  10) TMP = 0.55
                if ($14 <   9) TMP = 0.65
                if ($14 <   8) TMP = 0.7
                if ($14 <   7) TMP = 0.85
                if ($14 <   6) TMP = 2
                if ($14 <   4) TMP = 2.5
                if ($14 <   3) TMP = 3
                if ($14 <   2) TMP = 5
                if ($14 <   1) TMP = 6
                print $0, TMP

RudiC · April 3, 2017, 12:11pm

Is it? How about a combination of Scrutinizer's and my approach? With a definition file like

and an adapted code like

awk '
FNR == NR       {CV[NR] = $1
                 SV[NR] = $2
                 MX     = NR
                 next
                }

                {for (i=1; $14<CV && i<MX; i++);
                 print $14, SV
                } 
' OFS="\t" definition data

, how would that satisfy your needs and lend itself to easy changes afterwards?

SDohmen · April 3, 2017, 12:32pm

rudic:

Is it? How about a combination of Scrutinizer's and my approach? With a definition file like

and an adapted code like

awk '
FNR == NR       {CV[NR] = $1
   SV[NR] = $2
   MX     = NR
   next
   }

   {for (i=1; $14<CV && i<MX; i++);
   print $14, SV
   } 
' OFS="\t" definition data

, how would that satisfy your needs and lend itself to easy changes afterwards?

That would be even easier and i see how i accidently messed up the code so it only worked with the example. With a seperate file it would be even easier as it only needs editing without touching the code.

The only thing i need to change is the seperator itself as everything we use has ; in it but that is a easy fix. Instead of the OFS i will use the FS = OFS = ";" line in the beginning. At the end i will use $1 and $2 so i can call this script from the other one.

Thank you for the even better version of the script.

A couple min ago i tested the script from you RudiC and it worked superb. My co-workers found it even easier to edit because everything was in a seperate file. Below is the full script as we use it now.

awk '
BEGIN           {FS = OFS = ";"
                print "Reference;EAN;Condition;Stock;Price;Deliverycode;;manufacturer;Titel;MPN;supplier_code;Sup Category;Bol Category;Cost;Marge;Dropship Cost;Bol Vast Comm;Bol Vari Comm;;;;Marge Berekend;CMD;CMD + Bol Comm"
                }
FNR == NR       {CV[NR] = $1
                 SV[NR] = $2
                 MX     = NR
                 next
                }

                {for (i=1; $14<CV && i<MX; i++);
                 print $0, SV
                }
' OFS=";" /srv/prijslijst/filters/marges.csv $1 > $2