1 Reply Latest reply: Dec 20, 2011 8:45 AM by Stefan Wühl RSS

    Determine missing lines in long list

      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

       

       

        • Determine missing lines in long list
          Stefan Wühl

          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