Hi, I'm trying to create a random number field and I noted that the RandInt(n) would be perfect, however, I would like to only like different numbers generated for different items and types. Is it possible to do this without summarizing the rows because I require them to be separated. Example below: Input: Item #Item NameTypeCompliments1ApplesGoodMangoes2NappiesGoodBeer3CokeBadChips1ApplesBadBread1ApplesGoodMangoes Desired Output: Item #Item NameTypeComplimentsRandom Number1ApplesGoodMangoes5062NappiesGoodBeer6793CokeBadChips121ApplesBadBread10751ApplesGoodChips506 Thanks

Random Number Generator for different items

Hi,

I'm trying to create a random number field and I noted that the RandInt(n) would be perfect, however, I would like to only like different numbers generated for different items and types. Is it possible to do this without summarizing the rows because I require them to be separated. Example below:

Input:

Item #	Item Name	Type	Compliments
1	Apples	Good	Mangoes
2	Nappies	Good	Beer
3	Coke	Bad	Chips
1	Apples	Bad	Bread
1	Apples	Good	Mangoes

Desired Output:

Item #	Item Name	Type	Compliments	Random Number
1	Apples	Good	Mangoes	506
2	Nappies	Good	Beer	679
3	Coke	Bad	Chips	12
1	Apples	Bad	Bread	1075
1	Apples	Good	Chips	506

Thanks

Preparation

Accepted answers

Ned

What you are looking for is basically a checksum instead of a random #. An MD5 is a random # that is tied to specific input values. Normally it returns the number as a hex string, but that is easy to convert to a number in Alteryx:

HexToNumber(Left(MD5_UNICODE([Field1]+[Field2]),7))

This will give you a random # between 0 and ‭268435455‬. If you want the random number to be within a specific range (say 0 - 1000), that is easy with math:

HexToNumber(Left(MD5_UNICODE([Field1]+[Field2]),7))*1000/268435455

All comments

joe_strellis

I believe you could do it where you sort the data on item name and type. Allocate a random number to each record and then use the multi-row formula to look back to the previous record and allocate the random number from above if the two fields are the same otherwise keep the existing one.

I however think it would be easier to take a separate stream, summarize to the two fields you want, allocate the random number and then join this back to your original dataset on those two fields.

Both methods should work, let me know if you need an example of either.

jdunkerley79

I have a small macro which I have used to generate unique sequences before.

I placed in the gallery at https://gallery.alteryx.com/#!app/Permutation/565eed05aa690a1254277b2c

You can use it to generate a large enough set of random numbers and then join to the input set.

Had misread the problem. Ned's solution is a good one.

Random Number.yxmd

Ned

HexToNumber(Left(MD5_UNICODE([Field1]+[Field2]),7))

This will give you a random # between 0 and ‭268435455‬. If you want the random number to be within a specific range (say 0 - 1000), that is easy with math:

HexToNumber(Left(MD5_UNICODE([Field1]+[Field2]),7))*1000/268435455

Thank you so much - it worked great! Smiley Very Happy

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703