Determine missing lines in long list

[#1]

Hello,

here is the list with orders:

S11111-001

S11111-002

S22222-001

S22222-002

S22222-003

S22222-004

etc

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:

S44444-056

S44444-057

S44444-060

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.

Something like:

Table1:

LOAD

PO,

left(PO,6) as PONumber,

right(PO,3) as POAddendum

From...

(SORT BY????);

STORE Table1 into [something.qvd];

Drop this and load again

LOAD

PO,

PONumber,

POAddendum,

if(PONumber = PREVIOUSLY LOADED PONumer, if POAddendum = PREVIOUSLY LOADED POAddendum +1, PONumber,"OK"),"OK") as Missing

FROM something.qvd;

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

[swuehl · Accepted solution]

I think you are almost there...

TablePO:

LOAD * INLINE [

PO

S11111-001

S11111-002

S22222-001

S22222-002

S22222-003

S22222-004

S22222-006

S33333-002

S44441-001

S44441-003

S44441-002

S44441-006

S44441-007

];

ResultPO:

LOAD PO,

if (previous(left(PO,6))=left(PO,6),

    if(right(PO,3)=previous(right(PO,3))+1,'OK','Miss'),

    if(right(PO,3)='001','OK','Miss_Start')) as Missing

resident TablePO order by PO;

In addition, I check for missing numbers at the start of a PO sequence. You could also separate PO number into order number + addendum like you suggested, but you could also do it with one field like shown.

Hope this helps,

Stefan