3 Replies Latest reply: Feb 24, 2010 8:35 AM by Anita Fuchten RSS

    LOAD with a sum / counter?? how to loop??

    Anita Fuchten
      <pre>I have the following data:
      <pre>ServiceOrderRepeat:LOAD *,IF ( ([Serialnumber] = previous ([Serialnumber])) and Serviceordernr <> previous(Serviceordernr) and (addmonths(Orderdate_origineel, -6) < previous (Orderdate)), 1,0) as RepeatCounter,LOADServiceordernr,Serialnumber,OrderdateRESIDENT ServiceOrdersORDER BY Serialnumber, Orderdate, Serviceordernr;
      JOINLOAD *,IF ( ([Serialnumber] = previous ([Serialnumber])) and Serviceordernr <> previous(Serviceordernr) and (addmonths(Orderdate_origineel, -6) < previous (Orderdate)), 1,0) as RepeatCounter2,LOADServiceordernr,
      Serialnumber,OrderdateRESIDENT ServiceOrdersORDER BY Serialnumber, Orderdate DESC, Serviceordernr;

      Which results into the following (sample) data:
      ServiceOrders
      ServiceordernrOrderdateSerialnumberRepeatCounterRepeatCounter2
      REP93944413-11-2009123-456-78901
      REP94643111-01-2010123-456-78911
      REP95033004-02-2010123-456-78910
      <pre>

      If a serialnumber is entered in an order within 6 months, this is called a repeat ...
      Now I'm looking for a way of entering a total number of repeats ..

      <table><tbody><tr><td>ServiceOrders</td></tr><tr><td>Serviceordernr</td><td>Orderdate</td><td>Serialnumber</td><td>RepeatCounter</td><td>RepeatCounter2</td><td>TotalRepeatCounter</td></tr><tr><td>REP939444</td><td>13-11-2009</td><td>123-456-789</td><td>0</td><td>1</td><td>0</td></tr><tr><td>REP946431</td><td>11-01-2010</td><td>123-456-789</td><td>1</td><td>1</td><td>1</td></tr><tr><td>REP950330</td><td>04-02-2010</td><td>123-456-789</td><td>1</td><td>0</td><td>2</td></tr></tbody></table>
      Any suggestions??