Alteryx Designer Desktop Ideas

I have a process that joins 3 data sets to identify a specific group of data and apply certain ruling. From this created file, I need to extract the data (not the headings) from specific columns and insert into an already existing template. The template has formatting that needs to remain in order for it to function. 

Is this possible? 

I propose an update to the Join Tool to allow users to select "Only right" or "only left" fields after a join.  I like the options you have there currently (e.g., select highlighted fields), it would just be nice to add these options to it. 

Hi team,

I have a set of data that contains functional mapping of cost centers of the organisation. Such mapping is maintained by an administrator in the system and he manages and modifies/add changes.

Administrator does not consider the case sensitivity and sometime may write the same function name in UPPER and Proper case say for example "OPERATIONS" and "Operations"

I need to use such mapping in a workflow where I need to extract unique function names from the mapping.

Since a function has more than one cost centers so there are multiple lines for a function name in the mapping. 

I used Unique tool to do this. But this tool has a limitation that it considers the case sensitivity and reads the same word written in different case type as two unique values.

So in my case the i got two lines for the same function - one in UPPER case i.e. "OPERATIONS" and other with Proper case i.e. "Operations"

In order to tackle this problem, I used formula tool to convert entire text in uniform case type and then applied Unique tool.

Suggestion : I suggest enhancing the Unique tool to give user an option to configure it to consider case sensitivity while selecting unique values from the data.

When using ConsumerView macro from Join tool palette for demographic data matching from Experian, the matching yield is higher than compared to Business Match marco. It would be great if the matching key for telephone number could be added to Business Match (US) tool the yield might increase and will provide more value to the firmographic data sets than it currently yields by matching just the D&B Business Names and addresses only.

If you have a field length of say 10 in a Select Tool, then you use a Left Join tool and change that length to say 4. This turns that field red - as it should.  Then add a Select tool after the Union.  It should say 4 in the second Select tool.  But instead it says 10.  If it was changed to 10 (and it wasn't) then the field s/b red.

Today, any Alteryx tool with "Select" functionality has an option for "Dynamic or Unknown Fields" which, when checked, allows any new fields to pass through that tool.  This is a great function for most of the tools as you can allow workflow updates to pass through the tool without issue.

However, in the Join tool, there are some use cases where there is NEVER a reason to pass new fields from one side or the other into the tool, but you might still want new fields from a primary process.  Examples being something like a lookup/cross-reference to do an inclusive join, where adding new fields to the lookup might inadvertently pass these downstream.  Having the option to only allow unknown fields from one side through would greatly enhance this output.

Was thinking with my peers at work that it might be good to have join module expanded both for desktop and in-database joins.

As for desktop join: left and right join shows only these records that are exclusive to that side of operation. Would it be possible to have also addition of data that is in common?

As for in-db join: db join acts like classic join (left with matching, right with matching data). Would it be possible to get as well only-left, only-right join module?

I haven't been able to find a similar idea, but I have a scenario were I'm fully joining a dataset but I get a mismatch of column names.

When using the column renaming functionality in the Join tool, data sent through the Left or Right connector is not renamed. When I'm performing a Full Join the Union tool is adding these columns to the resulting table.

The renaming column functionality of the Join Tool should apply to all join types for consistency. My workaround has been to avoid renaming columns in the Join tool if aiming to perform a multi-join.

I came across the Find Replace Tool when I needed to find values from a column in one table in a column in another table. My first instance to solve the problem was to write a batch macro with a contains function in a formula followed by a not null filter (see attachment). This worked perfectly besides the fact that it was slow. Then I got excited when I discovered the Find Replace Tool accomplishes the same thing WAY faster, but I was wrong.

What I would love is the equivalent of an SQL query like this:

SELECT

    A.1

    B.1

FROM A

    INNER JOIN

        B

    ON A.1 LIKE "%" || B.2 || "%"

which is a legal query in SQLite and is equal to the output of the attached macro. This is what I wish the Find Replace tool could do (Or a different tool), but it only finds one instance per "Find Within Field" value. The tools decision making doesn't line up with the decision-making that I need, for example it doesn't return the longest values found, instead the one with the first key to appear in the field. One way I've found to configure it better is to string a number of these together, that will give me a better result but still won't find every instance and uses 90 or so tools when I feel I should only need 1-3 to accomplish the same thing.

Instead of an Inner Join, the Find Replace is more like of Left Outer Join followed by a Unique() on A.1. Is there a way to accomplish this out-of-database in Alteryx?

When using the Levenshtein distance matching in the fuzzy match it gives you the option to select a match threshold % but does not allow for other options.  Is it possible to provide these additional options in a future release?

Hello Alteryx,

In communicating with your agent Eddie Wong about a question (ref:_00DE0JJZ4._50044uMC7T:ref), he confirmed that Alteryx currently does not have an option to coalesce IDs when merging tables in the "Join" or "Join Multiple" tools.  This functionality is available in SQL and SAS, for example.

This code demonstrates it.

data d1;

      input ID age;

      datalines;

1 45

2 36

3 77

;

run;

data d2;

      input ID height;

      datalines;

1 138

3 176

5 197

;

run;

proc sql;

      select      coalesce(d1.id, d2.id) as id,

                  d1.age,

                  d2.height

      from  d1

                  full join

                  d2

      on          d1.id = d2.id;

quit;

Could you please consider adding this option into the "Join" and "Join Multiple" tools?

I have long and large workflows, IMO, that are getting difficult to follow. I'd like the ability to highlight the joins and set specific colors or at the very least highlight and toggle on/off highlights. I'd also like to be able to move my joins and so they are not curving all over the canvas.

I am aware there are posts on this in the community and that there is a macro available to do this on the public gallery, however I think this is such important functionality that it should be incorporated into the main product.  I want to be able to join 2 data sources by a date range.  In SQL the code would look like this:

select ric.*,map.*
from
Staging.TicksHourlySummary ric
LEFT OUTER JOIN Reference.ReutersInstrumentCodeMap map
on (
ric.#RIC = map.ReutersInstrumentCode
and ric.[Datetime] >= map.EffectiveFromDate
and ric.[Datetime] < map.EffectiveToDate

)

It would be immensely helpful if there can be a way to display the 'Manually Configure Fields' section of Union tool to the App user.

Something on the following lines - User uploads few files and then a window opens up onscreen which allows the user to 'manually configure fields' just like it is visible in the Union tool in a workflow. Its best use case is where we have a template with defined headers and the user just need to upload a new file/files and align the incoming fields correctly with the blank fields of a template.

I hadn't used the find/replace tool much until last Inspire conference when a presenter mentioned that it is more performant than a join.  After hearing this, I started to use the tool a bit more and can definitely see the value, especially in cases of partial matches.  That said, the one reason that I am always hesitant to use it over a join tool is that I don't have any way of seeing which records are unaffected by the find/replace operation without several additional tools.  The log gives me a record count, but in order to isolate records, I have to use a join, or filter which defeats the purpose of using the find/replace in the first place.  Also, if I have an automated process using a find/replace, I may just want to add a test or message tool to throw an error or warning if there are unmatched records (for example, if a new lookup value is added to a dataset).  

So I think that these shortcomings could be addressed in two ways:

1. Modify the find/replace tool to include a matched and unmatched output.  I realize that this one may be difficult given the configuration overhaul to a widely-used tool in the Alteryx ecosystem.  Furthermore, the requirement to union the matched and unmatched outputs may be viewed as some as a step backwards from a performance and simplicity standpoint in the tool.  

2. Add an error handling option to the tool itself (similar to the union) to give the user an option to how to handle unmatched records.  While this would only solve the concern over unmatched records slipping through the cracks, it would at least alert me that further investigation is required.

1. The Union tool 

When switching to Manual method and then adding fields up stream, the result is a warning "Field was not found". I don't look for warnings. This should create a red error. Having fields fall off the workflow is a pain.

2. Unique tool

Changing fields upstream causes the tool to error out when the workflow runs. No issues are shown before the run. 

3. Having containers all open up when I reopen a workflow is a nightmare when you have 20+ containers all over lapping.

It would be great if the deselecting of fields in a select tool updated the output window(before next run) as a "review" to make sure you are removing what you expect and/or you can see other items left behind that should be removed. This would also be useful for seeing field names update as  you organize and rename.

Often I join tables w/o pre-selecting the exact fields i want to pass and so I clean up at the end of the join. I know this is not the best way but a lot of times i need something downstream and have to basically walk through the whole process to move the data along.

I have found out a minor usability bug from v 11.0.5.26351.

Feature occurs if I output data from Join tool so that some or all left outputs are unselected:

Now when using the output of the left stream in another join there occurs a small bug affecting workflow:

In the rightmost join tool Alteryx does not recognize right away that there should be multiple columns, A, B and C coming from the left output. This can be solved by going out of the tool and getting back, but this has a minor impact on the workflow.

This is pretty straightforward:

The join tool right now does only inner joins, requiring the use of the union tool to emulate outer joins.

By itself it works fine, however when having workflows involving a large number of joins and unions with lots of data in it performance is severely impacted, leaving to situations where Alteryx Designer slows down massively even with extra CPU/RAM available.

Thank you and regards,

Marco Zara