14 Replies Latest reply: Jan 25, 2018 4:54 AM by RAVI SHINGE RSS

    For loop increment by every month

    RAVI SHINGE

      hello guys ,

       

      I need Value 12 for 1st four months and then another set of value  from 2nd months onwards and so on as shown below .

      I need to autogenerate value 12 four times every coming month and so on till 20 months .

       

       

      Months12345678910
      112
      21212
      3121212
      412121212
      5 121212
      6 1212
      7 12
      8 12
      9 12
      10 12
      12

       

       

      Here is my script it does not give me desired results . Could u plz help me with solution .  When i use Value and Month in the chart i will get value= 12 for 20 months . But i need something as shown in the matrix above .

       

      FOR i = 1 TO 20

      Let Duration=i;

      [XYZ]:

      LOAD

                 '12' as Value,

                 'Month' & $(Duration) as Month

                AutoGenerate 1;

                 

      NEXT i

        • Re: For loop increment by every month
          Youssef Belloum

          Hi,

           

          Try this:

           

          for i=1 to 10

           

               for x=1 to 4

           

               if $(i) =1 then

           

                    test:

                    load

                    $(i) as Month,

                    $(x) as Month2,

                    12 as Value

                    AutoGenerate 1;

           

               ELSE

           

                    test:

                    load

                    $(i) as Month,

                    $(x)+($(i)-1) as Month2,

                    12 as Value

                    AutoGenerate 1;

           

               ENDIF

           

               NEXT x;

           

          NEXT i;



          ==> create a pivot table with Month and Month1 as dimensions and sum(value) as expression

            • Re: For loop increment by every month
              RAVI SHINGE

              Thank you very much ! It worked like a charm

               

              i want to do a resident load . But i am not able to put it .

              Kindly let me know where do I put

                Resident [DataNew1];      in the script .

               

              Thanks,

              Ravi

                • Re: For loop increment by every month
                  Youssef Belloum

                  Example for RESIDENT:

                   

                  TABLE1:

                  load *

                  from...

                   

                  TABLE2:

                  load *

                  RESIDENT TABLE1;

                   

                  ==> the only condition is to load a table already loaded on the model.

                    • Re: For loop increment by every month
                      RAVI SHINGE

                      Thanks for reply !

                       

                      Yes i have a loaded table which is [DataNew1]

                      Now i want to Resident this in the script what you suggested but I am not able to put   Resident [DataNew1];  in the script .

                      Where should i write Resident [DataNew1];  in the above script ? I mean after End if or after Autogenerate or ....?

                        • Re: For loop increment by every month
                          Youssef Belloum

                          I used Autogenerate because I started with your script..

                           

                          but you can't use resident with autogenerate.

                           

                          you must know: if you want to generate the MONTHS/Value table or the table already exists on the model.

                           

                          the best you can do is to attach your App if there is no private data.

                            • Re: For loop increment by every month
                              RAVI SHINGE

                              Hello ,

                               

                              [DataNew1]:

                              LOAD

                                  "Product Line",

                                  "Impact month",

                              FROM [lib://AttachedFiles/Complexity_Mix.xlsx]

                              (ooxml, embedded labels, header is 1 lines, table is Model);

                               

                              For i=1 to 10

                                   For x=1 to 4

                                  

                                   if $(i) =1 then

                               

                                        test:

                                        load

                                        $(i) as Month,

                                        $(x) as Month2,

                                        "Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

                              //           12 as Value

                                        AutoGenerate 1;

                                 

                                   ELSE

                                        test:

                                        load

                                        $(i) as Month,

                                        $(x)+($(i)-1) as Month2,

                                           "Impact month"    I need this "Impact month"  field from [DataNew1]:  (Impact month has value 12)

                              //           12 as Value

                               

                                        AutoGenerate 1;

                               

                                   ENDIF

                               

                               

                                   NEXT x;

                               

                              NEXT i;

                               

                               

                              So how do I get this "Impact month"  field in the loop ?

                                • Re: For loop increment by every month
                                  Youssef Belloum

                                  I should modify the script to add another column with an autogenerate..

                                   

                                  can you show what is the expected result with this new column ?

                                   

                                  because I'm already generating 2 months columns

                                    • Re: For loop increment by every month
                                      RAVI SHINGE

                                      Ok !

                                       

                                      If you see initially what we did in the script is

                                            '' 12 as Value ''


                                      This value 12 is actually in the field named as [Impact Month ]

                                      So rather than mentioning 12 as Value  , I wanted to  include Field [Impact Month ] in the script .

                                      Bcoz now it has value 12 , 2moro it may have some other value so as to make it more dynamic .

                                      And [Impact Month ] is in the 1st Table named as [DataNew1]:   

                                      So was trying to use Resident , But i am not able to

                                      Thnx

                                        • Re: For loop increment by every month
                                          Youssef Belloum

                                          here is the new code:

                                           

                                          [DataNew1]:

                                          LOAD * Inline [

                                          "Impact month"

                                          12

                                          ];

                                           

                                           

                                          let vImpact_Month= Peek('Impact month',0,DataNew1);

                                           

                                           

                                          TRACE $(vImpact_Month);

                                           

                                           

                                          for i=1 to 10

                                           

                                           

                                          for x=1 to 4

                                           

                                          if $(i) =1 then

                                          test:

                                          load

                                          $(i) as Month,

                                          $(x) as Month2,

                                          $(vImpact_Month) as Value

                                          AutoGenerate 1;

                                           

                                          ELSE

                                           

                                          test:

                                          load

                                          $(i) as Month,

                                          $(x)+($(i)-1) as Month2,

                                          $(vImpact_Month) as Value

                                          AutoGenerate 1;

                                           

                                          ENDIF

                                           

                                          NEXT x;

                                           

                                          NEXT i;

                                            • Re: For loop increment by every month
                                              RAVI SHINGE

                                              Thank you very much Youssef .

                                              I really appreciate your patience in helping me out .

                                               

                                              Thanks a lot ,

                                              ravi

                                                • Re: For loop increment by every month
                                                  Youssef Belloum

                                                  You're welcome

                                                   

                                                  Good luck !

                                                    • Re: For loop increment by every month
                                                      RAVI SHINGE

                                                      Hello Youssef , I come back to you again with a requirement .

                                                       

                                                      I have used for loop with specific number such as

                                                       

                                                      For for x=1 to 4 ,  How to use For loop with column name , I mean the value 4 is in column name "Duration"


                                                      I wanted to use something like this :-

                                                      For x= 1 to Duration

                                                      How to do this ?

                                                        • Re: For loop increment by every month
                                                          Youssef Belloum

                                                          It is like what we have done for the Impact_month:

                                                           

                                                          [DataNew1]:

                                                          LOAD * Inline [

                                                          "Impact month"

                                                          12

                                                          ];

                                                           

                                                           

                                                          let vImpact_Month= Peek('Impact month',0,DataNew1);

                                                           

                                                           

                                                          Duration_table:

                                                          LOAD * Inline [

                                                          "Duration"

                                                          4

                                                          ];

                                                           

                                                           

                                                          let vDuration= Peek('Duration',0,Duration_table);

                                                           

                                                           

                                                          TRACE $(vImpact_Month);

                                                           

                                                           

                                                          for i=1 to 10

                                                           

                                                           

                                                          for x=1 to $(vDuration)

                                                           

                                                          if $(i) =1 then

                                                          test:

                                                          load

                                                          $(i) as Month,

                                                          $(x) as Month2,

                                                          $(vImpact_Month) as Value

                                                          AutoGenerate 1;

                                                           

                                                          ELSE

                                                           

                                                          test:

                                                          load

                                                          $(i) as Month,

                                                          $(x)+($(i)-1) as Month2,

                                                          $(vImpact_Month) as Value

                                                          AutoGenerate 1;

                                                           

                                                          ENDIF

                                                           

                                                          NEXT x;

                                                           

                                                          NEXT i;

                                                            • Re: For loop increment by every month
                                                              RAVI SHINGE

                                                              Hello Youssef,

                                                               

                                                              Could you kindly help me with it .

                                                               

                                                              With the reference to the above table which I have already attached  in spreadsheet .

                                                              I need to read each and every record of this table and fetch (Duration Month) and (Impact Month Value )

                                                              I have written a code kindly check the script and please do see comments .

                                                               

                                                              [Project1]:
                                                              LOAD
                                                                    "Product Line",
                                                                  "Complexity Level",
                                                                  Complexity,
                                                                  "Projects Per Month",
                                                                  "Flow3 Hrs per",
                                                                  "Average Flow3Hrs",
                                                                   "Duration (Month)",
                                                                  "Impact month"


                                                              FROM [lib://documnets/Complexity.xlsx]
                                                              (ooxml, embedded labels, header is 1 lines, table is Model);

                                                               

                                                               

                                                              For n= 1 to $(vDATA_COUNT)-1    // Should  read all the rows of the table  . Is it correct ?

                                                               


                                                              let vDATA_COUNT = NoOfRows('Project1');
                                                              Let vProductlines=Peek('Product Line',Project1,$(n));
                                                              let vcomplexity=Peek('Complexity Level',Project1,$(n));
                                                              Let vDurationMonthFlow3=Peek("Duration (Month)",,Project1,$(n));
                                                              let vImpactmonthFlow3=Peek('Impact month',Project1,$(n));

                                                               

                                                               

                                                                  For i=1 to 36   // Need Total months as 36
                                                                  
                                                                   For x=1 to $(vDurationMonthFlow3)   // Should read through all record values of ("Duration (Month)"

                                                               


                                                                   if $(i) =1 then

                                                               

                                                                       test:
                                                                        load
                                                                        $(i) as Month,
                                                                        $(x) as Month2,
                                                                        $(vFlow3Impact_Month) as value   // Should read all record values of 'Impact month'
                                                                        AutoGenerate 1;
                                                                 
                                                                   ELSE
                                                                    test:
                                                                        load
                                                                        $(i) as Month,
                                                                        $(x)+($(i)-1) as Month2,
                                                                          $(vFlow3Impact_Month) as value   // Should read all record values of 'Impact month'

                                                               
                                                                        AutoGenerate 1;
                                                                   ENDIF
                                                                  
                                                                   NEXT x;
                                                                 

                                                              Next i ;

                                                               

                                                              Next n;

                                                               

                                                               

                                                              This code is perfect to my requirements . Its just That it needs some correction specially For Loop

                                                               

                                                              Finally I need only Value and Month2 to draw my Line curve .

                                                               

                                                              Thanks,

                                                              Ravi