Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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,
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,
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
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,
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)
Yes, that's a typo, thanks for catching that.