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.

sebmueller87
Not applicable

How to loop through a dataset and use previously calculated values?

Hey there,

although I'm getting familar with QV and scripting I now have a question regarding Loops and Calculations in the script.

I want to perform a XYZ-Analysys. Therefore I have to compute some values....

Until now I do this steps in my script:

SOURCE:

LOAD ARTNR,

     WEEK,

     YEAR,

     WEEK &'_'& YEAR AS WEEK_YEAR,

     DAN &'_'& WEEK &'_'& YEAR AS ID,

     FORECAST,

     SALES

FROM

[..\SOURCEDATA\XYZANALSYSE\SALES.xlsx]

(ooxml, embedded labels, table is SalesJan2014)

WHERE YEAR = 2014;

LET $(SQ) = 0;

LET $(SF) = 2


For each ID in SOURCE

LEFT JOIN (SOURCE)

       LOAD

       ID,

       0.5*($(SQ)+($(SF)*FABS(1-(SALES/FORECAST)))) AS COEFFICIENT

       RESIDENT SOURCE;

NEXT

So what I want to calculate is the following:

SQt =  0.5*($(SQt-1)+($(SF)*FABS(1-(SALES/FORECAST))))

In the first period (calendarweek 1) SQt-1 vanishes the value 0 as set above the loop. As a result of the formula I get a value for SQ1 for the first calendarweek. Now I want to calculate SQ2 for the calendarweek 2. Now my SQt-1 should vanish the result of the formula from the previous period (calendarweek 1). This I want to do for every week in a year. I always use the result from the previous period in the calculation for the actual period.

But I do not know, how to do this in a Script:/

Thanks in advance for any ideas

Kind regards,

Sebastian

3 Replies
Not applicable

Re: How to loop through a dataset and use previously calculated values?

It is a necessary to pre calculate on the script level ? If not please do these calculation on UI.

Also, Please share some sample data.

Not applicable

Re: How to loop through a dataset and use previously calculated values?

more Question:

Is ID is a unique key on the source data set ?

sebmueller87
Not applicable

Re: How to loop through a dataset and use previously calculated values?

Hey,

thanks for your reply. Its not necessary to do the calculation on the

script level. Well, for no reason I tend to do a lot of things in the script...

I create the ID from the articlenumber and the calendarweek, so that it is

a unique one.