INCREMENTALLY DECREASE A THRESHOLD VALUE TILL A CONDITION IS MET

[Shahas]

Hi Everyone,

This is a repeat question as i couldn't get an answer earlier. Below is the workflow stage I'm at.

After this stage, I have to check for the sum of New Delta. If it's above 20k, the threshold of 'abs([Delta Adj])' has to be decreased 500 every time until the sum of New Delta is below 20k. 

I believe we need to use a iterative macro here. As I haven't worked using macro, would be grateful to anyone who can assist on the same.

Thanks in advance

[Pang_Hee_Choy]

@Shahas, yes, we are reducing values of Delta Adj by 500 in each row.

and, I tired on guessing. I more preferred tables.

Please provide tables in every step, with explanation.

[Shahas]

@Pang_Hee_Choy heyy... I will try to explain step by step.

After initial basic steps, I had to apply a threshold based on Delta Adj, ie, if its < 20k, the value will be 0 and rest every rows will be kept as it is.

This is the way I did it. At this point, data looks like this :

Original delta	Delta Adj
35000	35000
33568	33568
30000	30000
28000	28000
19450	0
18700	0
12200	0
9000	0
3500	0

Then the formula is applied, New Delta = Delta Adj+ Original Delta

Now the data looks like this : 

Original delta	Delta Adj	New Delta
35000	35000	70000
33568	33568	67136
30000	30000	60000
28000	28000	56000
19450	0	19450
18700	0	18700
12200	0	12200
9000	0	9000
3500	0	3500

Here onwards, we have to check the sum of New Delta. If its above 20k, the threshold which we applied in the 1st screenshot has to be reduced by 500 to 19500 and so on till we have the sum of new Delta < 20k.

So what I require is to have the threshold to be reduced incrementally till the sum of New Delta is below 20k.

Let me know if anything is not clear or any further details are required

[Pang_Hee_Choy]

@Shahas 

if I understand correctly.

at start, threshold for rows 20k

if total new delta >20k, threshold for rows will -500 to 19,500

until total new delta <=20k?

Original delta	Delta Adj	New Delta	remark
35000	35000	70000	delete as new delta more than 20k
33568	33568	67136	delete as new delta more than 20k
30000	30000	60000	delete as new delta more than 20k
28000	28000	56000	delete as new delta more than 20k
19450	0	19450	sum for first round, as threshold is 20k
18700	0	18700	sum for first round, as threshold is 20k
12200	0	12200	sum for first round, as threshold is 20k
9000	0	9000	sum for first round, as threshold is 20k
3500	0	3500	sum for first round, as threshold is 20k

till 3rd round, 19450 is exclude as threshold now is 19000, but sum of new delta is still 20k.

Original delta	Delta Adj	New Delta	remark
19450	0	19450	delete as new delta more than 19000
18700	0	18700	remain as threshold is 19000
12200	0	12200	remain as threshold is 19000
9000	0	9000	remain as threshold is 19000
3500	0	3500	remain as threshold is 19000

till the end, threshold for row is 12000, sum of new delta is less than 20k (Final output)

Original delta	Delta Adj	New Delta	remark
9000	0	9000	remain as threshold is 12000
3500	0	3500	remain as threshold is 12000

[Shahas]

@Pang_Hee_Choy 

"at start, threshold for rows 20k

if total new delta >20k, threshold for rows will -500 to 19,500

until total new delta <=20k?" - This is the logic

After every iteration, sum of new delta has to be checked.. 

So, from the example above, threshold of 12000 will be correct.

And in the output Original Delta, Delta Adj & New Delta should be present.

[Pang_Hee_Choy]

here the update version.

formula for first filter:

[New Delta] <= (20000 - (500 * [Engine.IterationNumber]))

x

[Shahas]

HEYY @Pang_Hee_Choy thanks for the updated version.

could u please explain at which step are we reducing the delta adjustment threshold? Just enquiring

[Pang_Hee_Choy]

the first filter.

[New Delta] <= (20000 - (500 * [Engine.IterationNumber]))

1st cycle = 20000 - (500 *0) = 20000

2nd cycle = 20000 - (500 *1) = 19500 

...so on