Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
afuchten
Valued Contributor

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

<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??
Tags (2)
3 Replies

LOAD with a sum / counter??

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!

afuchten
Valued Contributor

LOAD with a sum / counter??

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

afuchten
Valued Contributor

LOAD with a sum / counter??

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?

Community Browser