here is the list with orders:
Orders are comming every day and as you can see first 6 characters are order number (S11111 and S22222) and last three are addendum order. Those are scheduled orders so every time something is changed we get same order number but addendum is changed to next number. So, if we had S33333-056 on next change we will have S33333-057.
My question is: How can I check if I have orders with all addendum? There is a long list and for example I have:
When I check manually, I see that order S44444 does not have 058 and 059 addendum. The problem is that there is at least different 1000 orders, and they are not comming by some order. It is randomlly. One day we get S11111 and other S99999
MY idea (but I do not know how to implement it ) is: I need to create some LOAD statement that will sort by order number (field is named PO). Export that list in some qvd file. Drop table and load qvd file but now, because it is sorted by PO, and addendum all I need it to check if this line has same PO number as previous line. If yes then if this addendum is +1 than previous. If yes, nothing, if no write something (PO number + addendum that is missing + statement "NO"). This "somethning" will be field that I will use for selection i qlikview.
To solve the problem like this I need to sort by PO and addendum - how? and to check if order in this line is same as order in previous line plus if addendum in this line is +1 as addendum in previous line) See the reds one bellow.
left(PO,6) as PONumber,
right(PO,3) as POAddendum
STORE Table1 into [something.qvd];
Drop this and load again
if(PONumber = PREVIOUSLY LOADED PONumer, if POAddendum = PREVIOUSLY LOADED POAddendum +1, PONumber,"OK"),"OK") as Missing
Well, I know that I need to do make one more field because this IF statement is looking previous and I will have a problem if I am missing two or more, but that can be solved in if statement (I think).
If you have some other suggestion, it will be great