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

Designer| Re-ordering rows in data

Mj9715
8 - Asteroid

Hi All, 

 

I have a dataset (attached) that looks like below. Rows need to be re-ordered by col A as a group. That is, it needs to be 1month, 3months, 6months,12months and YTD. Can someone guide on this?

 

Input 

Col ACol B Col C
1 month 0.770.679
12 months0.090.755
3 months0.200.464
6 months 0.040.884
YTD0.800.931

 

Desired output 

Col ACol B Col C
1 month 0.240.697
3 months0.740.706
6 months 0.800.215
12 months0.680.884
YTD0.150.262
5 REPLIES 5
mst3k
11 - Bolide

lots of approaches, a couple i can think of-

 

make a formula tool with a switch or an if statement that assigns a number in order for each value- 

if Field1='1 month' then 1

elseif Field1='3 months' then 2

etc

 

then you can sort by this new field. 

you could also store the sort in a text input or an external file, where column A has "1 month" and column B has 1, then "3 months"/2, and so on. then join that to your data and again have a column with the sort order stored in it. the goal is to have an numeric field you can reliably sort by

 

if you want to get fancy, you could do something like a regex or substring formulas, or something to parse out the number from the value - i.e. the "1" from "1 month", etc. that wouldn't be the best solution though if it's possible you'd have other values like "2 years" or "12 weeks"

AngelosPachis
16 - Nebula

Hey @Mj9715 ,

 

You can use a lookup table for this, that will give you the correct order for each row.

 

Assuming that the lookup table looks like the one below

 

AngelosPachis_1-1632385016653.png

 

You can use a find and replace tool to append the order field in your main table and then sort based on that field.

 

AngelosPachis_0-1632384996056.png

 

Hope that helps,

Angelos

 

KarolinaRoza
11 - Bolide

hi @Mj9715 ,

 

I applied Sort tool for first column of data and it works. But as this column as string type of data it is not the best solution - it usually doesn't work as we expected.

You can create new column with numeric values for corresponding months : 3 Months -> 3 and sort by this column and Union it with YTD row.

 

KarolinaRoza_0-1632385094278.png

 

Regards,

Karolina

StephaneP
Alteryx
Alteryx

Hello, 

 

Same as @KarolinaRoza I think a basic SORT is working fine as your target respect the alphabetic order

StephaneP_0-1632385649343.png

 

Stéphane Portier
Sales Engineer
Alteryx
Mj9715
8 - Asteroid

Thanks all. All of them work, but Stephane's was easiest to use since it worked by using dictionary order. 🙂

Labels