Do Loop or While Loop in Macro Workbench

[former_member546432]

Hi All,

I am first time working with respect to DO Loop. I not getting correct syntax for DO or While Loop.

There is also not another requirement. I am working on Filter Detail level where I have few SKUs selected. I need to get the Number of Rows. This Filter Details number would dynamically change depending on selection profile. I want this count of row in the do or while loop.

I want to know how we can retrive the row count and also someone help me the correct syntax for DO or While loop. A snapshot of Do loop would be great help.

Thanks,

Best Regards,

Bhaskar

[former_member187488 · Accepted solution]

Hello,

I have a macro sample that clears all rows (6 rows) in a planning book, which uses DO loop. I could not get the WHILE loop work though the syntax is OK.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Ada,

Thanks for the reply. I tried a similar logic for my scenario but I am getting an Syntax Error.

I am attaching snapshot, Kindly have look and help me out on issue.

Thanks,

Best Regards,

Bhaskar

[former_member187488 · Accepted solution]

Hello Bhaskar,

You should take care of the level that you put the steps that are inside the LOOP.
For example, you should put the two steps "Calculate % Base Calculation" and "Loop+1" to the level the same as "DO" control. Now the steps are in an upper level than the controls.

Please refer to my attached screenshot previously.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Ada,

I tried this time taking in consideration of the level the loops are maintained. I created another macro but still I am getting an Error Message, This time with respect to Exit Function. I am uploading the photo pls help me out on the issue.

Thanks,

Best Regards,

Bhaskar

[rajkj · Accepted solution]

Bhaskar,

Per your macro definition based on the attached snapshot, there are few defects.

1. Action box - % Base Calculation, I guess you used it to enter your comments. For comments, you may try the condition . Action box needs to be used only for operations .

2.  Initialization of your variables LP and Rows using LAYOUTVARIABLE_SET. If you set these variables in the predecessor step, please ignore this comment.

3. Your loop implementation considers only the active column for specified number times based on the value of 'Rows' i.e.

if ( column_marked (active_column ) == 1 ) {

     do {

          row1 (active_column) = row2 (active_column) * x%

     } enddo ( 'Rows' times)

}

You may redefine this macro implementation taking into consideration of Ada's macro and above points.

Thanks,

Rajesh

[former_member187488 · Accepted solution]

Hello Bhaskar,

This is quite strange, because '.' is always added after condition statement by macro generation.
I don't see any chance that you get such problem ... This error should come from macro builder ...
Which support package are you using ?

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Rajesh,

Thanks a lot for the help.

I have defined the Rows, Percent and LP in first steps in the snapshot. I didnt expand as i felt that was not the cause that error.

Actually I need to help with your context.

I would explain my scenario. I first select a column and then customer pass a details of how many Detail Filter option they are using for running this scneario and then pass a %. Now I want the row numbers passed by customer for the DO times and run the logic for the calculation.

I can try your logic also but can we define in Control Statement as ENDDO 5(This is specificy 5 times to perform this logic)

I would try Ada logic also with operator after the control statement.

Thanks again for the efforts.

BEst REgards,

Bhaskar

[former_member546432 · Accepted solution]

Hi Ada,

THanks again taking timeout and help me out. We are using SCM 7.01. Is there any other way i can exit from the DO loop?

I would also try Rajesh effort like i would use an operator after the condition like

Operator: LAYOUTVAR_VALUE( 'LP' ) = LAYOUTVAR_VALUE( 'LP ')

Thanks,

BEst REgards,

Bhaska

[rajkj · Accepted solution]

Hi Bhaskar,

Thanks for the details. It really helped to understand your scenario.

1. Your user will select a time bucket (column). Assumption: User is already drilled down to 'Details All' level

2. Next, your user will choose appropriate key figures (rows) using "Details Filter"

3. They will pass a % to apply to all the chosen key figures. (Are you capturing this information at run time?)

Please let me know if the above rephrasing is correct.

If it is correct, I'm afraid, the whole approach needs to be changed.

Thanks,
Rajesh

[former_member187488 · Accepted solution]

Hello Bhaska,

I've made several tests in our internal system.
Uppon debugging, I find that if you put the control statements (including EXIT) in a 'STEP', the '.' will not be added automatically after some control statements like 'EXIT'. So please strictly follow my example:

1)Put the DO ... IF ... EXIT ... ENDIF ... ENDDO directly under the 'MACRO' element, instead of putting it under a 'STEP' element.
2)Put the calculation 'ROW: % Based Manual Adjustment' into one step, with 18 iterations.
3)Put action 'Loop counter + 1' into one step, with 1 iteration.

I hope you can get the macro correctly generated by doing the above.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Rajesh,

Your point is correct but only a small change on the point 2. The User loads the selection profile and goto the shuffler and uses Detail Filter option to select few SKUs for which they need to run the macro.

My approach is that I want to get the count of SKUs which has been selected and apply it in the DO loop. I expect the user to pass that value during run time. The user would pass that they have selected 5 SKUs and this integer value I assign it in Variable in Macro and pass it to DO loop and run it that time in the calculation.

Hope you got my point? Kindly revert back if you have any queries.

Thanks,

Best Regards,

Bhaskar

[former_member546432 · Accepted solution]

Hi Ada,

Thanks a lot. The logic worked but I am still facing the issue. When I am using a logic where it checks whether the column selected for running the Macro. I use IF Clause and Condition: COLUMN_MARKED( ACT_COLUMN ) = 1.

When I use this then i get an error message. I am enclosing the snapshot.

I removed that step and compiled and it worked fine but when I ran the macro, the macro is taking me into an infinite loop and i have to close the session and come out.

I am enclosing the snapshot of this issue also.

Kindly help me out on the issue.

Thanks,

Best Regards,

Bhaskar

[rajkj · Accepted solution]

Hi Bhaskar,

PLOBS_FOR_LEVEL( 1 ) gives you the selected number of SKU and NUM_VALUES_INPUT macro function helps to capture user input at run time.

One of my macros involving with similar task worked without any looping i.e. it will apply the factor to all selected PLOB. The only exception was filtering.

Take a backup of your macro and update it to just apply the percentage and remove rest of the lines

Step 1 : 18 iterations (or total number of columns)

Row - %Baseline manual adjustment = Row - Stat Forecast * LAYOUTVAR_VALUE( 'LP' )/100

Please let me know the result.

Thanks,
Rajesh

[former_member187488 · Accepted solution]

Hello Bhaskar,

Could you please expand your whole macro and show us the input details as well?
I think we'd better understand your scenario first than just check the macro design ... I still could not understand why you need to loop several times here ...... It looks strange to me.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Rajesh,

Thanks again for the efforts on helping me. But I think either I didnt understand your logic or you didnt get my Macro expectation.

We have already a Macro Running for this same logic but this macro runs in Agg level. When we run this at Agg level the value get disagg to detail level when drilled down.

What Customer wants now is: Lets say we have  100 SKUs and now customer wants to run this logic only on 50SKUs. Hence they do Detail Filter option and choose only 50 SKUs for which they want to run this Macro. In current Macro scenario we have to input 50 times the % change value to run the Macro for all the selected SKUs in detail filter. To avoid this i want to use DO loop and ask the customer to pass the number of rows which they select and also the %, this way with two input the macro would behave normally for Detail all Filter level too. This is the reason I want to pass DO loop.

Run time I am getting the value, that is not the issue. I need help with respect to DO Loop or if you have any other logic pls help me out. I really appreciate your effort and I would be grateful if you could help me out.

Thanks,

BEst Regards,

Bhaskar

[former_member187488 · Accepted solution]

Hello Bhaskar,

I think the scenario should be much easier than you thought.
You just use the same macro as you use for aggregated level.
For the macro configuration, please set it as only executed on 'Detailed Levels'.

Your user can perform drill down and SKU filter as they like, then they just select a column and execute the macro.

The macro will be executed for the selected SKUs that displayed on the planning book, and aggregated to aggregated level automatically.

No DO loop is needed here.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Ada,

I would explain my Scenario: We have a similar Macro which run this logic on Agg level where a % value is passed and it works fine and disagg as we want.

Currently customer wants new requirement: Lets say we have  100 SKUs and now customer wants to run this logic only on 50SKUs. Hence they do Detail Filter option and choose only 50 SKUs for which they want to run this Macro. In current Macro scenario we have to input 50 times the % change value to run the Macro for all the selected SKUs in detail filter. To avoid this i want to use DO loop and ask the customer to pass the number of rows which they select and also the %, this way with two input the macro would behave normally for Detail all Filter level too. This is the reason I want to pass DO loop.

I want this macro run only for the selected column not for all columns. Thats why i need to use COLUMN_MARKED( ACT_COLUMN ) = 1 check.

Hence I have included a new variable in Rows that is also passed by customer during the runtime as they do it for % value which is also passed. I would require to use this value in DO Loop and i would perform this action as many times its there in DO Loop.

Hope you got my logic. I would be happy if you could help me with any other logic also. I thought enhancing this macro would be enough. Currently this macro exit part is not working.

Kindly help.

Thanks,

BEst Regards,

Bhaskar

[former_member187488 · Accepted solution]

Hi, please refer to my above reply :

I think the scenario should be much easier than you thought. You just use the same macro as you use for aggregated level, without any change. Just make sure the macro is configures as only be executed on 'Detailed Levels'.

Your user can perform drill down and SKU filter as they like, then they just select a column and execute the macro. The macro will be executed for the selected SKUs that displayed on the planning book, and aggregated to aggregated level automatically.

No DO loop is needed here.

Best Regards,
Ada

[former_member546432 · Accepted solution]

Hi Ada,

Thanks a lot. Correct me if my understanding is wrong with respect to you context. You are saying that customer can drill down to any level and just execute the macro and it would work. If this was your are talking then you are right but the problem here is that if we execute the macro on detail level then the % value change is being asked as many times as there are filter option is there in the selection. Ex: If i have 50 SKUs then I have to input 50 times % value and it works fine.

If you want me execute the Macro if detail level. Customer wants to execute the Macro in all level not only for SKUs. SKU is an example, they can execute by Collection or style or Brand or any characteristic. Could you help me how i can put to many condition on drill.

Thanks,

BEst Regards,

BHaskar

[rajkj · Accepted solution]

Bhaskar,

It looks like you got stuck with user input capture. Have a simple flag in your logic to capture the user input only once for example..

1. Initialize a variable in a simple macro that added to 'Start' event

LAYOUTVARIABLE_SET( 'FLAG' ; 0 )

2. Your macro update

if( LAYOUTVAR_VALUE( 'FLAG' ) = 0 ) {

     //Capture user input

   LAYOUTVARIABLE_SET( 'FLAG' ; 1 )

}

if ( user input > 0 && flag value = 1 ) {

     //update the key figure values

}

Rajesh

[Former Member · Accepted solution]

Hello Ada,

I also have similar requirement on same line of thoughts but I wish the DO loop work or iterate left to right and not top to bottom.

I.e. In one of my requirement I want certain action to happen only for certain number of future buckets.

Answer to What that certain number is, is I have developed some code and am successfully able to bring that number -- can this number (for ex. 4) be used to do some calculation for next "4" buckets only using some kind of loop.

Ex. Now we have the number 4 and we want to add KF1 and KF2 to store value in KF3 only for those "4" buckets(columns) starting from current bucket (i.e current bucket + 3 future buckets).

In short I want to be able to loop column wise vis a vis as in your example macro loops from top to bottom and perform actions for 6 rows but iteration is only 1

Thanks.

Harshad M.

[former_member187488]

Regarding the row count question, sorry but I did not quite understand your purpose ...
It will be appreciated if you can make it clearer. Thanks.