pick random value of a column until x is reached

[df]

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

[Thableaus · Accepted solution]

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!

[df]

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

[df · Accepted solution]

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

[Thableaus]

Hi @df 

So happy that I made you learn something. This is the main point of my participation in the community.

I did not know about this condition (output value can't be greater than value). But good to know you found a workaround.

You can export your workflow as a .yxzp package. 

Cheers,

[Thableaus]

@df 

I also recommend you to mark your own post with the full solution as a topic solution.

Cheers,