Help making itteration macro

Question

Hi

I had some help from in here trying to create an itterative macro. And ive seen all the video's today in here, but I still cant make it work 😕

I have 2 dataset:
1. Purchase with a weigh (calcweight).
2. Priceagreement with a weight band ex. 0-30kg. So I want the macro to take data from stream 1 and find the price where its between weight(min) and weight(max) in our price agreement?

WeightToPrice.yxzp

SeanAdams · Answer

Hey @Hamder83

I think that you are very close to the solution:

- Give each purchase line a unique ID with something like the record ID tool (we need this later)

- as you say - join each purchase line to each pricing band using the append tool.   If you have 5 pricing bands; and 100 purchase lines - you'll have 500 rows

- Filter down to those where the purchase line is within the band (maximum of 100 rows).

- Join back to your data set in step 1 using record ID to find items that didn't have a pricing band - you can then give these a default pricing

Because you're doing this based on sets of data - this should operate much quicker than any iterative or looping strategy.

apathetichell · Answer

My strategy would be to create a row for each weight covered by a range in your weight range spreadsheet - therefore 0-30 for example would have 30 ranges. I can then match this with your weight primary key (billable? calculated?) in your workflow. NOTE. rows which don't exactly match your other criteria will not be joined. They'll be on the right anchor. Perhaps some of these are bulk rates or fall under other categories? You can re-union them back.

It would look something like this...

weight to price edits.yxzp

Hamder83 · Answer

Hi

We have different weight bands depending on what product im validating. So the main purpose is to validate the weight - in the range stated in the price agreement without having to define different formulas.

So lets say a package weighs 1 kilo.

And our weight band is like this:
0-1

1-2

2-5

5-10

and so on.

And I do:
calcweight > weightfrom and

calcweight <= weightTo and

Then it generated 5 rows, where I can filter out the one I need.

The 4 others filtered out by the filter. So all is good.

But if I have a package that weighs 11 kilo its also filtered out, and ends in the same stream and those?

ive attached some sample data, I hope it makes sense?

sampleflow.yxzp