topic LOAD with a sum / counter?? how to loop?? in QlikView

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

Anonymous — Wed, 24 Feb 2010 12:06:28 GMT

<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
Serviceordernr	Orderdate	Serialnumber	RepeatCounter	RepeatCounter2
REP939444	13-11-2009	123-456-789	0	1
REP946431	11-01-2010	123-456-789	1	1
REP950330	04-02-2010	123-456-789	1	0

<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??

LOAD with a sum / counter??

Miguel_Angel_Baeyens — Wed, 24 Feb 2010 12:11:50 GMT

Hello,

You can do that in a very similar way to SQL, using

LOAD ... , Sum(RepeatCounter2) AS TOTAL

using then GROUP BY clause, although I would use a graph (either a striaght or pivot table) to show more accurate results unless you already have a complex/stored procedure in your data source computing that total (which seems difficult to me as you are creating that field in QlikView).

Hope that helps!

LOAD with a sum / counter??

Anonymous — Wed, 24 Feb 2010 12:45:47 GMT

I really need the totals in the table, for correct displaying the numbers.

When selecting the month februari I need to see the number 2 ...
If I don't have a total in the table and use the RepeatCounter the number for februari will be 1 ... because the other serviceorders belong to other months ...

LOAD with a sum / counter??

Anonymous — Wed, 24 Feb 2010 13:35:55 GMT

Is there a way to use a while expression in order to get the total count??

Need some sort of loop to get the previous orders from the last 6 months (starting with the orderdate) ...

Can anyone help me fix this?