Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

afuchten
Not applicable

sum in the load statement ... need some help

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

Tags (1)