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

[#1]

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!

[sunny_talwar]

Can you may be share a sample data in Excel file with the expected output? Will be easier to help that ways

[#3]

Unfortunately I cannot find a way to post an attachment on the forum, maybe because I am still a beginner on the forum...?

https://www.wetransfer.com/downloads/affff7b2ab24c3a6163d018d285fe81e20160428153913/f3851e

Sorry

[sunny_talwar]

Try this

Uploading a Sample

[#5]

Thanks!

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD SKU,

    Lot_Number,

    Order_Numvber,

    AutoNumber(SKU&'|'&Lot_Number&'|'&Order_Numvber) as Key,

    [Current Status]

FROM

[Explanation Forum QV.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  If(Key = Previous(Key), Previous([Current Status]), 0) as [Previous Current Status]

Resident Table

Order By Key, [Current Status];

DROP Table Table;

[#7]

Really nice. Very straightforward and easy.

Is it correct to assume that this works the way it does due to the Order by function? Where you place the data in such a format that always finds the correct previous.

For instance if I would have a dataset with statuses that do no follow eachother up logically, like (4,3,5,7,2), but I have timestamp field I could just finish off with Order By Key, [Time Stamp]?

What is the function of the autonumber? And would it also be possible to use previous2... So that the script looks back to rows etc. etc. ?

Thanks a lot already!!!

[sunny_talwar]

Answer to your first question - Yes, order By is very important here. You can do it by TimeStamp which is a much better way to do it. Lack of a date field, I chose to do it by Current Status.

Answer to your second question - AutoNumber creates a number for a single value to a particular combination. For example if for ABC|DEF in field A and B it will always assign a single value whenever it finds this value.

Answer to your third question - You can use Previous(Previous()) for going back twice or you can play around with Peek() function which allows for a more dynamic way to look back and doesn't have to be repeated.

Hope this helps

[#9]

Thanks!

[sunny_talwar]

No problem at all