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

Unique tool with Dynamic or Unknown Options

vepp
8 - Asteroid

Hello All,

 

Does anyone has Macro or Idea to select "Dynamic or Unknown Fields" in Unique tool?

My required is to pass any  number  of fields and get unique records. Every time my input fields would changes

 

Please help.

 

Regards,

Vp

11 REPLIES 11
JosephSerpis
17 - Castor
17 - Castor

Hi @vepp  I came up with an alternative solution and ensured the fields in the Unique tool are the same but can handle if the number of fields decrease or increase. Let me know what you think?  

danilang
19 - Altair
19 - Altair

Hi @vepp 

 

The solution from @JosephSerpis  is close, but it fails if you include field types other than numeric types.  This is because the Crosstab tool needs to be reconfigured if the Values column contains anything other a number.  You can get around this by joining with the original data on RecordID instead of using a CrossTab.    The J output has the Unique records from the original data set and the R output contains the duplicates

 

WF.png

 

Dan

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi Vepp,

 

The macro below will summarise on all fields and return your data back to its original schema so you can use the macro in the middle of your workflow if needed with no issues, therefore should be exactly what you're looking for, if this solves your issue please mark the solution as correct, if not let me know!

 

Regards,

Jonathan

 

Summarise On All Fields.PNG

danilang
19 - Altair
19 - Altair

Hi @Jonathan-Sherman 

 

Since you're using a Crosstab to rebuild your data instead of just using the record ids to join to the original data, you'll have problems if the original column names have spaces or non alpha-numeric characters in them.

 

Input DataInput Data

 

Output dataOutput data

 

Dan

 

vepp
8 - Asteroid

Thanks a lot for suggestions, @JosephSerpis ,@danilang  It includes _ in column names.  All my inputs are Excel based and it contains all type of characters.  Just to avoid this, I have to do lot of process.  Is there any other method using macro?

 

@Jonathan-Sherman - Well highlighted. Looks like we don't have simple solution for  Dynamic fields.

 

danilang
19 - Altair
19 - Altair

Hi @vepp 

 

The solution I provided handles all field types(with the possible exception of blobs) and doesn't have the underscore issues since it doesn't perform a crosstab to rebuild the data.  Try it out on your data and let me know.  Converting it to a macro would simply involve changing the input and browse tools to a macro input and a macro output.

 

Dan

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @vepp,

 

I'll work on it this evening and post an updated macro for you to solve the issues highlighted,

 

Regards,

Jonathan

danilang
19 - Altair
19 - Altair

Hi @Jonathan-Sherman 

 

If you just remove the 2 cross tabs from yours and join the unique ids from each of 2 streams to the original data, before the transpose,  you'll remove the field rename issue.

 

Dan

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @vepp,

 

I think I've got to the bottom of it, unless @danilang can see any other issues? It changes the column headers back to their values prior crosstab. Attached is my updated macro. If this solves your issue please mark the solution as correct, if not let me know!

 

Regards,

Jonathan

 

Summarise On All Fields.PNG

Labels