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

Keep only Unique Rows

ZoeM
8 - Asteroid

Hello Community!

I have a simple one in illustration, but tough to implement for my level. 

In the attached, I have a very simple data set of phases a vehicle goes through, Stations 1 - 3.

If an issue is raised at a station, it is categorized as Uncontained.

If a raised issue has a solution identified at the station, it is categorized as Contained.

if a raised issue is resolved at a station, it is categorized as closed. 

At each station, for Closed issues,  I would like to keep it where it closed out. 

So in the sample, Station 1 had 1 Uncontained 1 Contained and 1 Closed.

Station 2 had 1 Uncontained 1 Contained and 1 Closed

Station 3 had 1 Uncontained 1 Contained and 1 Closed

 

uniquely to each station. 

 

As the issues go through the different stations theyre categories will change and I do not want to duplicate the results for when an issue was resolved (closed out).

 

I hope my explanation and desired results work?

 

Thanks in advance Team!

9 REPLIES 9
cplewis90
13 - Pulsar
13 - Pulsar

Hey @ZoeM,

 

I think I got what you are going for. I went through a workflow and not sure if you wanted an aggregation at the end, but you can always adjust that. Here is a picture:

keep uniques.PNG

ZoeM
8 - Asteroid

I like it!

I just have to tweak it now for my data set. help me understand something, why did you use the first formula tool and do the 'tonumber' formula?

cplewis90
13 - Pulsar
13 - Pulsar

I used it to create a field that was numeric from the station number. I was grabbing just the number out of "Station 1" but in using a right formula it still creates a string, so to number converts it to be numeric.

ZoeM
8 - Asteroid

Do you mind helping me with something?

My actual Phase names are string with no numerical value. 

In your workflow, how would that impact the creation of the Min Phase Number field?

Say the phases were called One, two, Three, etc, how would that impact the creation of the Min Phase Number field? I think thats the key for me, otherwise your workflow is what Im looking for. 

cplewis90
13 - Pulsar
13 - Pulsar

If the phrases were "One, Two, Three, etc" I would adjust the formula to a switch statement )or potentially a translation type file. To show the switch statement (would replace the formula in the first formula tool of the workflow):

 

Switch([Field1], null(), "One", 1, "Two", 2, "Three", 3, "Four", 4, "Five", 5)

 

You could also ensure capitalization doesn't impact you by using this formula:

Switch(uppercase([Field1]), null(), "ONE", 1, "TWO", 2, "THREE", 3, "FOUR", 4, "FIVE", 5)

 

Let me know if you have any other questions. More than happy to help

 

Best,
Chris Lewis

ZoeM
8 - Asteroid

Interesting...

So, let me throw you a curve ball. The Phases sometimes switch depending on the date. So my data is sorted by date to determine which one comes first etc.

 

So I do not think the switch would be the best option because I cannot define which one is number 1 2 3 4 5 etc...

 

 

cplewis90
13 - Pulsar
13 - Pulsar

Is the field a datetime field or just a date field? 

 

If it is datetime, then you could use that as your min instead of trying to create a phase number that is a numeric data type.

ZoeM
8 - Asteroid

Yes its a date field.

So it would be 4/8/19 for the first step, 5/9/19 for the second etc...

ZoeM
8 - Asteroid

Thanks Chris! Its working as a charm! I used the date field and thats exactly what I wanted.

I appreciate your promptness

Labels