Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Help making itteration macro

Hamder83
11 - Bolide

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?

 

 

6 REPLIES 6
Luke_C
17 - Castor

Hi @Hamder83 

 

Based on your description I think an iterative macro might be overengineering this. Unless I'm missing something, why not just join up the data and use a filter tool to filter the records where the calcweight is less than the weight(max) and greater than the weight(min)?

Hamder83
11 - Bolide

Hi

That is what im doing today, and I really dislike this solution.
If i have 1 purchase order with 1 weight, let's say 2.25 kilo.

And I have a purchase agreement
0-0,25 kg
0,25-0,5 kg
0,5-1

1-2

2-5 and so on.

And im runnung hundrede of thousands of lines through this method. Then I end up with millions of records.

And I feel like its hard making an overview over what passes through, and if I have lines outside my weight agreement, since all the incorrect lines passes thorugh there too.

 

apathetichell
18 - Pollux

Hi,

 

Can you explain what your macro is supposed to do? If you are looking for if a value is within a range there are better ways to find it than to use an iterative macro. much better ways. An iterative macro is best thought of as Alteryx's version of recursion - and it's best used when the underlying data is going to change between runs. Ie values are being popped out of a queue/discarded. It's best with fewer fields and really clear rules. I find it easier to jettison almost everything except a recordid and whatever fields I need specifically for the macro.

 

This is the filter tool in your batch macro and I'm not sure it's what you want..

if
[CalcWeightPurchase] = 0
THEN

[CalcWeightPurchase]>=[WeightFrom(excl)] AND [CalcWeightPurchase]<= [WeightTo(Incl)]

ELSE
[CalcWeightPurchase]>=[WeightFrom(excl)] AND [CalcWeightPurchase]<= [WeightTo(Incl)]
ENDIF

 

Also your macro will stop running once the exit target whether number of iterations or filter is engaged... Also you are joining your data in the workflow and then rejoining in the macro which you don't need to do.

Hamder83
11 - Bolide

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?

 

apathetichell
18 - Pollux

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...

SeanAdams
17 - Castor
17 - Castor

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.

Labels