sum in the load statement ... need some help

Anonymous — Thu, 25 Feb 2010 08:38:05 GMT

Hi all,

In the LOAD statement I want to sum a RepeatCounter for each row looking back 6 months ...

For each row I need a sum of the following:

The Orderdate of the lookup records should be between Orderdate of the current row and Orderdate - 6 months
and the SerialNumber should also be the same ... (this indicates there is a repeated repair which we want to count)

I already have some counters which I created, but this is the last counter needed ... and I can't find a good solution.

RepeatRepair:
LOAD *,
IF (
 (SerialNumber = previous (SerialNumber)) and
 Serviceordernr <> previous(Serviceordernr) and
 (addmonths(Orderdate, -6) < previous (Orderdate)),
 1,0) as RepeatCounter
;
LOAD
Serviceordernr,
SerialNumber,
Orderdate
RESIDENT RepeatRepairTemp
ORDER BY SerialNumber, Orderdate, Serviceordernr;


JOIN
LOAD *,
IF (
 (SerialNumber = previous (SerialNumber)) and
 Serviceordernr <> previous(Serviceordernr) and
 (addmonths(Orderdate , -6) < previous (Orderdate )),
 1,0) as RepeatCounter_repeatedSO
;
LOAD
Serviceordernr,
SerialNumber,
Orderdatum
RESIDENT RepeatRepairTemp
ORDER BY SerialNumber, Orderdate DESC, Serviceordernr DESC;

Anyone has suggestions on how to do this?? Is there a way of using a while loop??

thx

Anita

topic sum in the load statement ... need some help in QlikView

sum in the load statement ... need some help