9 Replies Latest reply: Apr 28, 2016 2:10 PM by Sunny Talwar RSS

    Lookup's/if's with restrictions...

    Maarten Borsten

      Hi guys,

       

      I could really use your help with the following:

       

      In words: I want a new field in my database which is equal to the following. Find the same combination of Order#, SKU#, LOT# and some some more and if found provide me with the data in field x, but it should be the highest found but lower than the value in field x for the specific row where you are looking it up for.... SAY WHAT!?!?

       

      In table the basis looks like this:

       

      Order

      SKU

      LOT

      XYZ

      Status 1

      QWERTY

      BLABLA

      1

      2

      A

      ...

      1

      R

      ...

      1

      2

      A

      ...

      2

      S

      Ikhuioh

      1

      2

      A

      ...

      3

      G

      Khie

      1

      2

      A

      ...

      4

      Y

      U89798

       

       

      So the new field I want to add in script gives me for line 3 from table above a 2, because that is the highest value smaller than it self matching in order, sku, lot, xyz.

       

       

      Total table looks like:

       

      Order

      SKU

      LOT

      XYZ

      Status 1

      QWERTY

      BLABLA

      Old Status

      Suggestion Formule wise...

      1

      2

      A

      ...

      1

      R

      ...

      0

      IF(Order+SKU+LOT+XYZ = Order+SKU+LOT+XYZ, than New Status = MAX status, but < Status 1, OR otherwise 0

      1

      2

      A

      ...

      2

      S

      Ikhuioh

      1

      IF(Order+SKU+LOT+XYZ = Order+SKU+LOT+XYZ, than New Status = MAX status, but < Status 1, OR otherwise 0

      1

      2

      A

      ...

      3

      G

      Khie

      2

      IF(Order+SKU+LOT+XYZ = Order+SKU+LOT+XYZ, than New Status = MAX status, but < Status 1, OR otherwise 0

      1

      2

      A

      ...

      4

      Y

      U89798

      3

      IF(Order+SKU+LOT+XYZ = Order+SKU+LOT+XYZ, than New Status = MAX status, but < Status 1, OR otherwise 0

       

      So what I am asking is two things.

       

      1. Validate if this is how it will work, and if it does,

      2. Rewrite the formula in a way that it works and can be pasted in the script.

       

      One important thing to note is that the database has some 20 million lines in there, so if it is a very heavy formula/task than this will not work as I am working on a laptop and not a killer PC.

       

      Looking forward to your suggestions! Thanks in advance!