Unfortunately I cannot find a way to post an attachment on the forum, maybe because I am still a beginner on the forum...?
AutoNumber(SKU&'|'&Lot_Number&'|'&Order_Numvber) as Key,
[Explanation Forum QV.xlsx]
(ooxml, embedded labels, table is Sheet1);
If(Key = Previous(Key), Previous([Current Status]), 0) as [Previous Current Status]
Order By Key, [Current Status];
DROP Table Table;
Community_214874.qvw 154.5 K
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!!!
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