There is a table that exists with many entries for unique orderIDs, (i.e. unique orderID 123 can have N number of entries). I wanted to compare the last 2 entries for each unique orderID that I have, and add a new column to my result set based on conditions that return true when the values are compared between each of the 2 entries for each orderID.
How can I do this? Here is what I tried so far but keep running into length errors with the second line of code:
myResult:select from myTable where ({x in -2#x;i) fby orderID; / this works by getting the last 2 entries for each unique orderID
/ trying to check if the second row's myType is a certain value, and if my first entry for myValue is equal to "0", and if the start time of my second row - the end time of my previous row is greater than a certain number of seconds
update didEventHappen:?[(next[myType] in `TYPE_A) and (myValue="0") and ((next[start.time] - end.time) > 00:00:01.000); 1b; 0b] fby orderID from myResult
Hard to know without a data sample but it looks to me like your issues might be:
For an update statement you don't use fby, you use by. You use fby in where clauses.
Is your myValue column a type char ("c") or type string ("C")? If it's string then your equality won't quite work but a raze can avoid the ambiguity.
So this should work (ignoring the timestamp clause for now, you can add that in):
myTable:([]orderID:100?til 10;myType:100?`TYPE_A`TYPE_B;myvalue:string 100?til 5)
myResult:select from myTable where ({x in -2#x};i) fby orderID;
q)update didEventHappen:?[(next[myType] in `TYPE_A) and raze[myvalue="0"];1b;0b] by orderID from myResult
orderID myType myvalue didEventHappen
-------------------------------------
8 TYPE_B ,"4" 0
3 TYPE_A ,"3" 0
8 TYPE_A ,"4" 0
3 TYPE_B ,"4" 0
1 TYPE_B ,"1" 0
7 TYPE_B ,"2" 0
7 TYPE_B ,"4" 0
2 TYPE_B ,"2" 0
5 TYPE_A ,"1" 0
9 TYPE_B ,"2" 0
2 TYPE_B ,"4" 0
9 TYPE_A ,"1" 0
4 TYPE_A ,"1" 0
0 TYPE_A ,"1" 0
4 TYPE_A ,"0" 0
5 TYPE_A ,"3" 0
6 TYPE_B ,"0" 1
6 TYPE_A ,"0" 0
0 TYPE_B ,"2" 0
1 TYPE_A ,"3" 0