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": FunctionIssuesExteriorPaint color is not availableExteriorDoor handle does not chargeInteriorSeat covers will not be available on timeInteriorGear does not stay in placeInteriorBack seat dampenerWheelsRim fitting titleWheelsTire tread wear too soonWheelsLug nuts sizingWheelsRim covers are too smallTrunkTrunk opens at 5 m/hTrunkSpare tire placementEngineHose clamps I now want to add a new field called "Points" for each Function: FunctionPointsExterior1500Interior1400Wheels1300Trunk1200Engine1100 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!

Do not duplicate values from new field

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":

Function	Issues
Exterior	Paint color is not available
Exterior	Door handle does not charge
Interior	Seat covers will not be available on time
Interior	Gear does not stay in place
Interior	Back seat dampener
Wheels	Rim fitting title
Wheels	Tire tread wear too soon
Wheels	Lug nuts sizing
Wheels	Rim covers are too small
Trunk	Trunk opens at 5 m/h
Trunk	Spare tire placement
Engine	Hose clamps

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

Function	Points
Exterior	1500
Interior	1400
Wheels	1300
Trunk	1200
Engine	1100

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!

Developer Tools

Common Use Cases

Join

Accepted answers

BrianR

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.

Non-DuplicatingPointsV2.yxmd

All comments

LordNeilLord

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

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

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703