For Next and filling with Details in Table

[#1]

For i= $(vCurWeek) to ($(vCurWeek)+3)

Table:
Load
 SID_0CALWEEK,//week same as $(vCurWeek)
 SID_CMATLOC,//Material
 Stock,
 Demand
Resident FactTable;


//Now I need to generate a Table for each week and for each Material with the sum of Stock.
//How to do???

next i;

[vgutkovsky · Accepted solution]

I'm not sure why you need a separate table for each. Why don't you just do 1 Group By over Week and Material, summing stock, and that will give you the sum at that level? You can then join this sum back into your original table.

Regards,

[#3]

Hi Vlad ,

Thanks for answer.

I am trying this and I wonder why this simple thing does not work:(

Temp:
Load
Distinct
 SID_0CALWEEK, //WEEk
 Material,/Material
 Sum([/BIC/KSTKBAL]) as SStock1
 Resident FactTable
 Group by SID_CMATLOC
;

I am getting this problem

Any Suggestions?

Regrads

Sravan

[ashfaq_haseeb]

Hi

Try this one

Temp:
Load
Distinct
only(SID_0CALWEEK) as SID_0CALWEEK, //WEEk
only(Material) as Material,/Material
Sum([/BIC/KSTKBAL]) as SStock1
Resident FactTable
Group by SID_CMATLOC
;

Hope that helps

Regards

ASHFAQ

[vgutkovsky]

Ashfaq's solution won't work either (incorrect syntax). This should do it:

mytable: //"Table" is a keyword, so don't name your table that
Load
 SID_0CALWEEK,//week same as $(vCurWeek)
 SID_CMATLOC,//Material
 SID_0CALWEEK & '|" & SID_0CALWEEK as temp_key, //not strictly necessary, but I like to group over 1 key whenever possible
 Stock,
 Demand
Resident FactTable;
LEFT JOIN (mytable) LOAD
 temp_key,
 sum(Stock) as [Weekly Material Stock]
RESIDENT mytable
GROUP BY temp_key;
DROP FIELD temp_key FROM mytable;

Regards,

[#6]

Hi Vlad,

Both Work same. Thankyou very much Guys

Now I have a Bigtask of passing these values to variables as in the script. Never did such kind of loops and scripting. Hence it is becoming Tough for me!

Hope anyone can give tips!

Calculation of Coverage based on 7 days per week:

Script Calculate Coverage()

For i = 0 to "End of Periods"
 Stock = ST(i)

If Stock <= 0 Than
 CV(i) = 0
 Stop Script
 End If

 Demand = 0
 Cover = 0

 For j = i+1 to "End of Periods"
Demand = Demand + DM(j)
 If Demand >= Stock Than
 Demand = Demand - DM(j)
 Rest = Stock - Demand
 If Rest > 0 Than
 Cover = Cover + (7 * Rest / DM(j) )
 End If
 CV(i) = Cover
 Stop Script
 Else
 Cover = Cover + 7
 End If
 End For

 If Stock > Demand Than
 Cover = 999
 End if

End For

End Script

Normal 0 false 21 false false false DE X-NONE X-NONE

Example:

The Period on Y Axis 0 to 4 represent Weeks $(vCurrWeek) to $(vCurrWeek)+3

In this example the Coverage of period 0 is 14, i.e. CV(0) = 14. The coverage of period 1 is 8,4, i.e. CV(1) = 8,4.

Calculation example:

1st FOR:
1st iteration:
// Calculate Coverage of Period 0: CV(0)
i = 0
Stock = ST(0) = 200
Demand = 0
Coverage = 0

2nd FOR:
1st iteration
j = 1
Demand = 0 + DM(1) = 0 + 100 = 100
Cover = 0 + 7 = 7
2nd iteration
j = 2
Demand = 100 + DM(2) = 100 + 100 = 200
Demand = 200 - DM(2) = 200 - 100 = 100
Rest = Stock - Demand = 200 - 100 = 100
Cover = Cover + ( 7 * Rest / DM(2) )
= 7 + ( 7 * 100 / 100 ) = 14
CV(0) = Cover = 14

2nd iteration:
// Calculate Coverage of Period 1: CV(1)
i = 1
Stock = ST(1) = 120
Demand = 0
Coverage = 0

2nd FOR:
1st iteration
j = 2
Demand = 0 + DM(2) = 0 + 100 = 100
Cover = 0 + 7 = 7
2nd iteration
j = 3
Demand = 100 + DM(3) = 100 + 100 = 200
Demand = 200 - DM(3) = 200 - 100 = 100
Rest = Stock - Demand = 120 - 100 = 20
Cover = Cover + ( 7 * Rest / DM(3) )
= 7 + ( 7 * 20 / 100 ) = 7 + 1,4 = 8,4
CV(1) = Cover = 8,4

3rd iteration:
// Calculate Coverage of Period 2: CV(2)

[llauses243]

Hi Vlad,

Is this error ?

Good luck, Luis

[vgutkovsky]

Yes, that's a typo, thanks for catching that.