Hello guys, i have a table with 3 columns of different dimensions and a column with values. Sum_Value has the grouped Value by Dimension a. Weight is different fo each Dimension a. Now I'm searching for a methode which randomly allocate the Values until the Sum of this new column reached Sum_Value_Weighted. Is there a way to do that? Dimension aDimension bDimension cValueSum_ValueWeightSum_Value_WeightedOutput_Value (Example)Output_Value (Example 2)a1b1c1390.4412a1b2c1290.4410a1b3c1190.4401a1b1c2190.4411a1b2c2190.4400a1b3c2190.4410a2b1c1270.8622a2b2c1170.8610a2b1c2270.8612a2b2c2270.8622

pick random value of a column until x is reached

Hello guys,

i have a table with 3 columns of different dimensions and a column with values.

Sum_Value has the grouped Value by Dimension a.

Weight is different fo each Dimension a.

Now I'm searching for a methode which randomly allocate the Values until the Sum of this new column reached Sum_Value_Weighted.

Is there a way to do that?

Dimension a	Dimension b	Dimension c	Value	Sum_Value	Weight	Sum_Value_Weighted	Output_Value (Example)	Output_Value (Example 2)
a1	b1	c1	3	9	0.4	4	1	2
a1	b2	c1	2	9	0.4	4	1	0
a1	b3	c1	1	9	0.4	4	0	1
a1	b1	c2	1	9	0.4	4	1	1
a1	b2	c2	1	9	0.4	4	0	0
a1	b3	c2	1	9	0.4	4	1	0
a2	b1	c1	2	7	0.8	6	2	2
a2	b2	c1	1	7	0.8	6	1	0
a2	b1	c2	2	7	0.8	6	1	2
a2	b2	c2	2	7	0.8	6	2	2

Tips and Tricks

Accepted answers

Thableaus

Hi @df

I'm not sure if I followed, but I developed a solution with an Iterative macro.

Please check the workflow and also the macro and let me know if you have any doubts.

Just pointing out that the "Output Value" column is going to change dynamically every time you run the workflow, because a new value is going to be randomly generated.

Cheers!

MacroExample - Random Allocate Values.yxzp

@Thableaus hello again,

i developed a own solution with a batch macro (level 1) & a iteration macro (level 2)

each dimension a is a batch.

in each batch i select a random row until condition is reached (enought values picked). When row is selected i check if value is under weighted_Value if yes weighted_value + 1
if not weighted_value

Then weighted_value cant exceed value.

I attached the macros and workflow but there are a little bit messy. (created some columns i did not need ect.)
Just started with macros today

Thanks, your answer inspired me to learn this macro stuff.

Edit: Oh, I can't attach files
i get error "The contents of the attachment doesn't match its file type."

Edit 2: I'll attached some pictures just in case someone has same problem.

Edit 3: I have attached the file now. I changed it a little bit so the screenshots look a little bit different compared to the file attached.

MacroExample - Random Allocate Values v2.yxzp

All comments

Thableaus

Hi @df

I'm not sure if I followed, but I developed a solution with an Iterative macro.

Please check the workflow and also the macro and let me know if you have any doubts.

Just pointing out that the "Output Value" column is going to change dynamically every time you run the workflow, because a new value is going to be randomly generated.

Cheers!

MacroExample - Random Allocate Values.yxzp

Hi @Thableaus

thanks for the answer.

I think i was i little bit to fast with accepting it as a solution. The Output Value should never be higher than the column 'Value'.

The Idea is to say, that we have a sum of x Values ('Sum_Values') in column 'Value' for each Dimension a.
We weight the Sum_Value with the Weight -> y 'Sum_Value_Weighted'

Now i know, that i want to randomly select y Values from the column 'Value'.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703