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

Iterative Macro with Random Sampling

analyticon
7 - Meteor

Hello All... Not new to Alteryx but just learning marcos. I would appreciate your help on this problem. Its kind of like a Monte Carlo simulation but much more simple. I have a table with dimensions (length,width,height) and weight. I want to take a random sample of this table until I reach a dimensional threshold (l+w+h) to be determined. I would like to add the weights of each row in the sample and then repeat until the table is empty. Any dimensions that are already over threshold are filtered out already. The outputs will be number of items in groups and total weight. Input table looks like this:

 

ItemHEIGHTLENGTHWIDTHWEIGHT
a1114.2511.2529.37
b726169.22
c7261631.83
d1021.51745.92
e15211830.78
f12201411.49
g1220143.7
h15211824.29
i1324.2516.258.33
j1114.2511.254.34
k1021.51713.1
l12201444.16
m5.3124.886.184.1
n8.515.9810.5111.07
o15211825.38
p726165.56
q15211815.56
r726164.63
s14.2115.214.6112.63
t1114.2511.2511.11
u15211821.61
v9.3720.5515.5913.75
w4.0921.619.023.64
x4.921.2516.251.9
y15211845.35
z621153.17

 

I have been looking at the tutorials and other workflows but can't seem to get exactly what I want. Your help is greatly appreciated!

 

Thanks!

J

10 REPLIES 10
Thableaus
17 - Castor
17 - Castor

Hi @analyticon 

 

How does output table look like? Do you have the dimensional threshold to simulate the iterations? I wasn't able to fully understand what you want.


Cheers,

mmenth
11 - Bolide

Hey @analyticon,

 

Try using the attached iterative macro. I'm not sure how you want to handle the threshold exactly--this macro will keep adding items until the threshold has been reached, so the solutions it outputs are over the threshold that I set. If you want solutions to be under the threshold, you might have to tweak the macro a bit. I can help you further if that's what you need.

 

Best,

mmenth

danilang
19 - Altair
19 - Altair

hi @analyticon 

 

Here's a different way to go about it. 

 

WF.png

 

The part of the main workflow before the macro randomizes the order of the list. 

 

Macro.png

 

The Macro performs iterative running totals until all the records have been processed.

 

 

Dan

 

analyticon
7 - Meteor

Thanks for the quick replies!!! Sorry I wasn't more clear. From the table above, I am looking to create a intermediate table to aggregate from. So first random sample the data and then subtotal until limit is reached, once reached (not exceeds) 100 for example, then it becomes a "group" and the aggregate the groups. So the first output would be this:

ItemHEIGHTLENGTHWIDTHWEIGHTDimDim_TotalGroup
a1114.2511.2529.3736.536.51
b726169.224985.51
c7261631.8349492
d1021.51745.9248.597.52
e15211830.7854543
f12201411.49461003
g1220143.746464
h15211824.29541004
i1324.2516.258.3353.553.55
j1114.2511.254.3436.5905
k1021.51713.148.548.56
l12201444.164694.56
m5.3124.886.184.136.3736.377
n8.515.9810.5111.0734.9971.367
o15211825.3854548
p726165.5649499
q15211815.56545410
r726164.63494911
s14.2115.214.6112.6344.0293.0211
t1114.2511.2511.1136.536.512
u15211821.615490.512
v9.3720.5515.5913.7545.5145.5113
w4.0921.619.023.6434.7280.2312
x4.921.2516.251.942.442.414
y15211845.355496.414
z621153.17424215

 

And the final output would look like this:

ItemGroupTotalWeight
1238.59
2277.75
3242.27
4227.99
5212.67
6257.26
7215.17
8125.38
915.56
10115.56
11217.26
12336.36
13113.75
14247.25
1513.17

 

Note though that I did not random my results like i wanted to as I was just creating this quickly in excel.

 

Thanks again!

J

danilang
19 - Altair
19 - Altair

hi @analyticon 

 

The data stream before my final summarize tool is in the intermediary format with no groups bigger than the max. (Field names are a little different, but I'm sure you can fix those)

 

Dan

mmenth
11 - Bolide

In this case I don't think you need a macro, you can just randomize the sort first, then apply your grouping method through a couple of multi-row formulas. See attached!

analyticon
7 - Meteor

I like both solutions thanks! I was pretty close in @mmenth solution before i thought I needed a macro. I also forgot a key detail (sorry folks!) for my threshold, I need it to be either before it reaches the threshold or the total in the group is <= 4. I think I can do this as a multirow formula.

analyticon
7 - Meteor

@danilang I have been working with the macro you posted and its very helpful. I can get it to work with my dimension threshold or my total item threshold but not both. I think I may need to create a macro within a macro. Basically i need it to run row by row and accumulate the total rows (items) up to 4 OR until the dimension threshold is reached. For example, if the threshold is 275 in the example above, the output would look like this:

ItemHEIGHTLENGTHWIDTHWEIGHTDimDim_TotalItemTotalGroup
a1114.2511.2529.3736.536.511
b726169.224985.521
c7261631.8349134.531
d1021.51745.9248.518341
e15211830.78545412
f12201411.494610022
g1220143.74614632
h15211824.295420042
i1324.2516.258.3353.553.513
j1114.2511.254.3436.59023
k1021.51713.148.5138.533
l12201444.1646184.543
m5.3124.886.184.136.3736.3714
n8.515.9810.5111.0734.9971.3624
o15211825.3854125.3634
p726165.5649174.3644
q15211815.56545415
r726164.634910325
s14.2115.214.6112.6344.02147.0235
t1114.2511.2511.1136.5183.5245
u15211821.61545416
v9.3720.5515.5913.7545.5199.5126
w4.0921.619.023.6434.72134.2336
x4.921.2516.251.942.4176.6346
y15211845.35545417
z621153.17429627

Thanks again for your help.

danilang
19 - Altair
19 - Altair

Hi @analyticon 

 

A small change to the macro gives you what you want.

 

NewMacro.png

 

Add a ItemCount field and change the filter condition to 

 

[RunTot_TotalDimension] <= [MaxDimension]
And
[ItemCount]<=4

 

Dan

Labels