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

HOW TO NTH AND SORT YOUR DATA QUESTION

jdelaguila
8 - Asteroid

Alteryx Community,

 

I have a file with 1,000 records in it and I need to split it up into 4 sections - Package A, Package B, Package C and Package D.

 

At the same time i need to make sure that 2 fields in my data ([FILENAME] and [STATE]) get as close as possible to an even split between all 4 packages. 

 

So [FILENAME] would be 1st Priority and [STATE] would be second priority. 

 

I started with a sort tool - but can't seem to find the right tool to do the NTH'ing and update my [PACKAGE] field. 

 

Thoughts - Recommendations?

 

Thanks again for all the help.

 

Javier

 

 

 

5 REPLIES 5
clmc9601
13 - Pulsar
13 - Pulsar

Hi @jdelaguila,

 

This is one of my favorite uses for the Tile tool. If you're ok with mostly even groups and group 1 having the most unique values, Tile is a quick and easy option for you! If your use case needs to be more exact, there are other solutions, too. Just thought I'd suggest the easy one first!

 

Since Tile has a group by function, your state and filenames will be more or less equally distributed across the five groups. Since it starts assigning values starting with group 1, group 1 will have the most unique values.

 

Hope this helps!

 

Screen Shot 2021-03-09 at 5.50.18 PM.png 

jdelaguila
8 - Asteroid

So 1st of all thank you for showing me the TILE tool. Looks like a pretty powerful tool and possibly the answer.

 

With that said its not quite doing what i need it do.

 

1st - If i have 1000 records - i need the field to have an even split between the 4 packages - so the result would be an 250,250,250,250.

 

2nd - The 1st priority field [FILENAME] would be evenly split between those packages. So for every [FILENAME] its split up evenly within those packages. So if Filename 1 has 100 records - it will split up Filename 1 into 25,25,25,25 

 

3rd - The 2nd priority field [STATE] would be evenly split between those packages. So for every [STATE] its split up evenly within those packages. So if State (CA) has 100 records - it will split up CA into 25,25,25,25.

 

As an example Visual you can see how the data below was split into 4 packages - A,B,C,D.

FILENAMESTATEPACKAGE
0185CAA
0185DCB
0185MDC
0185NMD
0185VAA
0200CAB
0200DCC
0200MDD
0200NMA
0200VAB
0411CAC
0411DCD
0411MDA
0411NMB
0411VAC
0936CAD
0936DCA
0936MDB
0936NMC
0936VAD

 

If we analyze the data above our packages are evenly split:

PACKAGECount
A5
B5
C5
D5

 

If we continue to analyze the data - FILENAME(our 1st Priority in the Nth) - is split up within those packages. 

So FILENAME=0185 had 5 records - it split them up into each package with a small deviance.

FILENAMEPACKAGECount
0185A2
0185B1
0185C1
0185D1
0200A1
0200B2
0200C1
0200D1
0411A1
0411B1
0411C2
0411D1
0936A1
0936B1
0936C1
0936D2

 

And finally if we dig deeper into [STATE] - our 2nd priority field - we can see how our data is split up into those packages. 

So we had 4 CA records - each one got a Package.

STATEPACKAGECount
CAA1
CAB1
CAC1
CAD1
DCA1
DCB1
DCC1
DCD1
MDA1
MDB1
MDC1
MDD1
NMA1
NMB1
NMC1
NMD1
VAA1
VAB1
VAC1
VAD1

 

Sorry for all the writing and hope this makes sense. If not i can try and re-word it differently.

 

Javier

clmc9601
13 - Pulsar
13 - Pulsar

Hi Javier,

 

Thank you for being so clear in your desired outcome! The documentation you provided in your second workflow was very helpful as well. I believe several additional tools are needed due to the specificity of your request. I hope the results of this are exact enough to suit your use case! Happy to revise if necessary.

 

Screen Shot 2021-03-09 at 8.11.20 PM.png

clmc9601
13 - Pulsar
13 - Pulsar

Hi @jdelaguila,

 

Did this second version work for you? Thanks! 

jdelaguila
8 - Asteroid

Yep worked! Thanks so much!

 

Javier

Labels