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

Do not duplicate values from new field

ZoeM
8 - Asteroid

Hi Community.

I have a simple but unique situation where I am trying to layer in a new workflow to an existing larger data set.

 

I want to join the data set on the unique field "Function":

 

FunctionIssues
ExteriorPaint color is not available
ExteriorDoor handle does not charge
InteriorSeat covers will not be available on time
InteriorGear does not stay in place
InteriorBack seat dampener
WheelsRim fitting title
WheelsTire tread wear too soon
WheelsLug nuts sizing
WheelsRim covers are too small
TrunkTrunk opens at 5 m/h
TrunkSpare tire placement
EngineHose clamps

 

I now want to add a new field called "Points" for each Function:

 

FunctionPoints
Exterior1500
Interior1400
Wheels1300
Trunk1200
Engine1100

 

When I use the Join Tool, it adds the Points to each line item for issues which ends up duplicating the values.

 

Any ideas on a workaround?

 

Thanks for your help!

8 REPLIES 8
LordNeilLord
15 - Aurora

Hi @ZoeM

 

Not sure what you mean, if you want points next to each item then the join tool is what you need.

 

Do you need to split the points up against each item, so each exterior would have 750?

 

An example of the output you're expecting would be a great help

ZoeM
8 - Asteroid

The data is being fed into QlikView and there are views for the data by Function against Issues and also Function against Points.

You may be right that the Join Tool would be the best choice and then additional data manipulation to be done in QVW, however I thought there would be some tool I could use to make isolate the new field "Points".

BrianR
Alteryx
Alteryx

Hi, since you are joining on the Function field, the Join tool will do just that as you have found. Can you share what the end result would look like? i.e., in the example you shared, how would you like to assign the points - is it once per function area? - example...

 

Exterior - 1500 (regardless of how many issues there are)

Interior - 1400 (regardless of how many issues there are)

 

or is it a sum of all the points, but only the Function area and the sum of points? - example...

 

Exterior - 3000

Interior - 4200

 

Thanks

ZoeM
8 - Asteroid

Exterior - 1500 (regardless of how many issues there are)

Interior - 1400 (regardless of how many issues there are)

 

Exactly, that is my desired end result because the visualization is specific to the "Points" field

BrianR
Alteryx
Alteryx

So I might be missing something here in what you ultimately are looking for, but take a look at the attached workflow. I created two tables to match your samples. Note in the 2nd table (the Points table), I added another Row as an illustration. Using the Join tool, as you have seen, joining on Function alone will yield duplication of all matching row Point values. Using the Summarize tool, I used the First aggregation function to just call out the Function Area and it's Point Value - not the Sum of all the Points. 

 

The reason for adding in that additional row is to illustrate the concept of matching rows - i.e., if you were only after the Point value, you could simply just output the result of that Point table...however, it would output each and every row regardless if you had any issues in a given area. So the Join tool is important here to isolate the rows where you have matching Function areas, and the Summarize tool will just list the "base" Points when it finds a match, regardless if there is 1 issue or 100 - via the First aggregation action.

 

ZoeM
8 - Asteroid

Thanks for the explanation. 

How would I also be able to have the Issues included in the final data set? 

There will be two visualizations, the first will be Function - Issues, and the second Function - Points. Its almost as if I would need to alter the data when it is fed into the next tool to get the desired end result?

BrianR
Alteryx
Alteryx

OK, got it. Take a look at this version - it produces a single table with both the Issues, Functions, and then just one value for Points per Functional area. This should eliminate the double counting issue - I put a test Summarize tool to illustrate that...and if you drop an Output tool after the main branch, you should be able to use this in your Visualization tool to show not only the issues, but the Points, but only at a grouped, Functional area that won't duplicate them.

 

 

ZoeM
8 - Asteroid

Brian Excellent...

I will need to digest the steps, thanks for sharing the workflow and I will replicate for the data set!

Labels