Iterative Macro with Random Sampling

[analyticon]

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:

Item	HEIGHT	LENGTH	WIDTH	WEIGHT
a	11	14.25	11.25	29.37
b	7	26	16	9.22
c	7	26	16	31.83
d	10	21.5	17	45.92
e	15	21	18	30.78
f	12	20	14	11.49
g	12	20	14	3.7
h	15	21	18	24.29
i	13	24.25	16.25	8.33
j	11	14.25	11.25	4.34
k	10	21.5	17	13.1
l	12	20	14	44.16
m	5.31	24.88	6.18	4.1
n	8.5	15.98	10.51	11.07
o	15	21	18	25.38
p	7	26	16	5.56
q	15	21	18	15.56
r	7	26	16	4.63
s	14.21	15.2	14.61	12.63
t	11	14.25	11.25	11.11
u	15	21	18	21.61
v	9.37	20.55	15.59	13.75
w	4.09	21.61	9.02	3.64
x	4.9	21.25	16.25	1.9
y	15	21	18	45.35
z	6	21	15	3.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

[Thableaus]

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]

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]

hi @analyticon 

Here's a different way to go about it. 

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

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

Dan

[analyticon]

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:

Item	HEIGHT	LENGTH	WIDTH	WEIGHT	Dim	Dim_Total	Group
a	11	14.25	11.25	29.37	36.5	36.5	1
b	7	26	16	9.22	49	85.5	1
c	7	26	16	31.83	49	49	2
d	10	21.5	17	45.92	48.5	97.5	2
e	15	21	18	30.78	54	54	3
f	12	20	14	11.49	46	100	3
g	12	20	14	3.7	46	46	4
h	15	21	18	24.29	54	100	4
i	13	24.25	16.25	8.33	53.5	53.5	5
j	11	14.25	11.25	4.34	36.5	90	5
k	10	21.5	17	13.1	48.5	48.5	6
l	12	20	14	44.16	46	94.5	6
m	5.31	24.88	6.18	4.1	36.37	36.37	7
n	8.5	15.98	10.51	11.07	34.99	71.36	7
o	15	21	18	25.38	54	54	8
p	7	26	16	5.56	49	49	9
q	15	21	18	15.56	54	54	10
r	7	26	16	4.63	49	49	11
s	14.21	15.2	14.61	12.63	44.02	93.02	11
t	11	14.25	11.25	11.11	36.5	36.5	12
u	15	21	18	21.61	54	90.5	12
v	9.37	20.55	15.59	13.75	45.51	45.51	13
w	4.09	21.61	9.02	3.64	34.72	80.23	12
x	4.9	21.25	16.25	1.9	42.4	42.4	14
y	15	21	18	45.35	54	96.4	14
z	6	21	15	3.17	42	42	15

And the final output would look like this:

ItemGroup	Total	Weight
1	2	38.59
2	2	77.75
3	2	42.27
4	2	27.99
5	2	12.67
6	2	57.26
7	2	15.17
8	1	25.38
9	1	5.56
10	1	15.56
11	2	17.26
12	3	36.36
13	1	13.75
14	2	47.25
15	1	3.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]

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]

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]

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]

@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:

Item	HEIGHT	LENGTH	WIDTH	WEIGHT	Dim	Dim_Total	ItemTotal	Group
a	11	14.25	11.25	29.37	36.5	36.5	1	1
b	7	26	16	9.22	49	85.5	2	1
c	7	26	16	31.83	49	134.5	3	1
d	10	21.5	17	45.92	48.5	183	4	1
e	15	21	18	30.78	54	54	1	2
f	12	20	14	11.49	46	100	2	2
g	12	20	14	3.7	46	146	3	2
h	15	21	18	24.29	54	200	4	2
i	13	24.25	16.25	8.33	53.5	53.5	1	3
j	11	14.25	11.25	4.34	36.5	90	2	3
k	10	21.5	17	13.1	48.5	138.5	3	3
l	12	20	14	44.16	46	184.5	4	3
m	5.31	24.88	6.18	4.1	36.37	36.37	1	4
n	8.5	15.98	10.51	11.07	34.99	71.36	2	4
o	15	21	18	25.38	54	125.36	3	4
p	7	26	16	5.56	49	174.36	4	4
q	15	21	18	15.56	54	54	1	5
r	7	26	16	4.63	49	103	2	5
s	14.21	15.2	14.61	12.63	44.02	147.02	3	5
t	11	14.25	11.25	11.11	36.5	183.52	4	5
u	15	21	18	21.61	54	54	1	6
v	9.37	20.55	15.59	13.75	45.51	99.51	2	6
w	4.09	21.61	9.02	3.64	34.72	134.23	3	6
x	4.9	21.25	16.25	1.9	42.4	176.63	4	6
y	15	21	18	45.35	54	54	1	7
z	6	21	15	3.17	42	96	2	7

Thanks again for your help.

[danilang · Accepted solution]

Hi @analyticon 

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

Add a ItemCount field and change the filter condition to 

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

Dan