7 Replies Latest reply: Apr 16, 2011 6:56 PM by Vlad Gutkovsky RSS

    For Next and filling with Details in Table

    Sravan Puppala

       


      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;



        • For Next and filling with Details in Table
          Vlad Gutkovsky

          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,

            • For Next and filling with Details in Table
              Sravan Puppala

              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

                • For Next and filling with Details in Table
                  Ashfaq Mohammed

                  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

                    • For Next and filling with Details in Table
                      Vlad Gutkovsky

                      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,

                        • For Next and filling with Details in Table
                          Sravan Puppala

                          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)

                          • For Next and filling with Details in Table
                            Luis Laura

                            Hi Vlad,

                            Is this error ?

                            Good luck, Luis