Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Unique and Only unique tool not giving correct result

Highlighted
8 - Asteroid

Hi all, I am trying to do a simple vlookup, for that, as per forum discussions, I am using a join tool with left and join both inputting in the Union tool.

Before that join, the 2 inputs are coming from 2 separate workflows.

Work flow 1: groups by store and SKU combination, it is supposed to be unique. But, it is not coming up as unique.

Work flow 2: again, categorizing the store and SKU combination as A, B & C. 

so, just wanted to map the store sku combination unique values to A, B, C like a vlookup function in excel.

 

I tried, unique tool, but from the output I see, there are many duplicate records.

Now tried only unique tool, which again shows duplicate when I try to remove duplicate through excel, due to that, the ABC mapping is not working.

 

I am not looking to use any particular function/tool here.

My end objective is to get the store, SKU combination unique values in WF 1.

WF2 is already giving me unique ABC for store SKU combination.

I just need to map it and publish the result.

saikathalder_1-1577961503348.png

 

 

How to fix this?

 

Edit: adding screenshot of the Only unique function parameters, Am I doing it correctly by selecting all, as I need the unique values?

saikathalder_0-1577962496198.png

 

 

Highlighted
Alteryx
Alteryx

Hi @saikathalder 

 

If you are selecting every field it will look across all of them to check for a unique value in each on each row, which may be why you are still seeing more on your combination.

 

You'll want to choose Store and SKU only, as that will then look for the unique combinations within just those two fields and ignore the other values.

 

I'd have thought you would want just the unique tool as well, not the only unique tool. The difference being:

 

Unique - selects the first unique instance of the fields selected and any duplicates are removed.

Only Unique - selects only the unique instances and all duplicates are removed.

 

So the only unique will remove Stores and SKU's that are duplicates, where as the Unique will keep the first instance of each.

Highlighted
8 - Asteroid

Thanks, got your point.

My data shows something like this below., I need to remove the whole row, it is duplicate and keep the whole row if the site/SKU combination is unique. In my first comment, I think I did not make it clear.

So, do you recommend this Unique tool or only unique function in this case. Thanks a lot

saikathalder_0-1577963171035.png

Highlighted
Alteryx
Alteryx

Ah got you.

 

Sorry if you want to remove all rows when there is a duplicate, it is the only unique you want to use.

 

For example.

Input Data:

StoreSKU
A1
A2
A3
B1
B1
B2
C1
C1
C1

 

U output of Unique

StoreSKU
A1
A2
A3
B1
B2
C1

 

U output of Only Unique

StoreSKU
A1
A2
A3
B2

 

Highlighted
8 - Asteroid

Hello, thanks for the example and explanation. I understood the difference. I kept the "only unique" function and ran the WF. but, I got the same result. Changes made:

1. Selected only Article (SKU) Site in the parameter of only unique.

2. Connected U to the L of the join.

3. Connected separate WF container ABC output to R of the join.

4. Then did Left + join and sent the output to the Union.

5. Finally browse to see the result.

In the join, I selected all the column from Left and from the right, I only selected the weightage and ABC value columns, and unchecked others.

Here are the screenshots.

ABC data.

saikathalder_0-1577964591572.png

 

Join condition.

saikathalder_1-1577964616110.png

 

only unique condition:

saikathalder_2-1577964649193.png

Workflow.

saikathalder_3-1577964673301.png

I am not sure, where is it going wrong.

Here is the sample output, all the required fields are empty, which I selected in the join condition.

saikathalder_4-1577964771323.png

 

Highlighted
Alteryx
Alteryx

Ah ha.

 

I think the issue might be with your Join Multiple before these stages.

 

I'd recommend using a few normal join tools instead, and tracking the data through those, as that would be my guess as to where it going wrong with the blank fields.

 

The join multiple isn't like the join tool and a union with regards to aligning columns (which can be why you end up with blanks when you might not expect them)

Highlighted
8 - Asteroid

Hey Joes, If you see the snapshot of the WF. There are no multiple joins, all are normal joins.

And I followed this existing answer for my workflow, where they used join and union.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Delete-duplicate-rows/td-p/356942

saikathalder_0-1577966418023.png

Highlighted
Alteryx
Alteryx

Hi @saikathalder 

 

It was a guess around the join multiple due to the rename of the fields "Input_#2_"

 

Has this not been caused by a join multiple prior to the snapshot of the flow you have been using?

 

Failing that, would you be able to upload your data snapshots? 

8 - Asteroid

Hey Joey, 

I did not use any mutiple join in my WF all are simple joins.

Yes, I am sharing the sample WF as well as data snapshot.

Highlighted
Alteryx
Alteryx

Hi @saikathalder 

 

With the sample data it's a little too hard to work out what's gone wrong.

 

But I still think it is the join multiples that are here in the "ABC_logic" container:

 

JoinMulitple.png

 

I'd recommend replicating that container using normal join tools as you will be able to track what's joining, what's not much easier.

 

As mentioned the join multiple doesn't behave like a join and union. So if you have unjoined fields, they end up in the output with blanks in fields with the same name but they get prefixed "Input_#2_" for example.

Labels