Alteryx Designer Ideas

Alteryx has different behaviours for conversion errors depending on the type of conversion desired. When converting from string to date data type, a conversion error will generate a NULL value. When converting from a string to a numeric data type, a conversion error will generate 0. Why the different behaviours? There is a lack of harmony here. 0 is a valid value and should not be the generated value for a failed string to numeric conversion. It should be NULL.

When I perform data type conversions, i do not apply them directly to the source field and then cast it. If there is a conversion error, then I have lost or corrupted the source information. Rather, I create a target field with the desired data type and use a formula to apply a conversion, such as datetimeparse or tonumber. Finally, I do a comparison of the source and target values. If the datetimeparse generated a NULL then I can PROGRAMMATICALLY address it in the workflow by flagging or doing some other logic. This isn't so easy to do with numerics because of the generated 0 value. If I compare a string "arbitrary" to the generated 0 value as a string then clearly these do not match. However, if I compare a scientific value in a string to the converted numeric as a string, then these do not match though they should. My test of the conversion shows a false positive.

I want a unified and harmonised conversion behaviour. If the conversion fails, generate a NULL across the board please. If I am missing something here and people actually like conversion errors to generate 0 please let me know.

Sometimes formulas get pretty long. There are cases of deeply nested conditionals, concatenation of long strings, cases where multiple casts and parses are used, etc. where formulas get pretty large and unwieldy. The current system of wrapping lines and managing the size of the properties pane can be a hassle, especially if you are trying to use any sort of whitespace formatting to make the formulas more readable.

My solution is this is pretty simple, add a pop-out window for formulas. It could be a context menu option from right-clicking the formula box itself, a button on the bar at the top of each formula, or any number of other things.

A really good example of this is MS Access. You can right-click any text box that takes an expression and open it in the expression editor pop-up window. The current system is more like excel where you're stuck with whatever box size you're given.

// This is my new formula
MAX([Price] * [Quantity],0)
// This was my old formula
// [Price] * [Quantity]

Imagine being able to SELECT your text block (could be many lines) and right-clicking to see an option to Comment or Un-Comment those configuration statements.  I thought that you'd like it too.

Cheers,

Mark

Currently zero-width spaces are not removed by the data cleanse tool. This means you can have two visually similar words like Dog and Dog but one is actually longer than the other due to zero width characters. The removal of these zero-width spaces can be added with the formula.

REGEX_Replace([_CurrentField_], "\x{200B}", "")

It would be most excellent if I could click directly into the "Enter Expression Here" dialog to start editting my custom filter, rather than having to click the "Custom Filter" radio button.

For me it typically requires three clicks to get going. I often start with a "Basic filter" then:

1) Click into "Enter Expression Here" dialog and nothing happens.

2) Remeber that I need to click the "Custom Filter" radio button.

3) Click back in the "Enter Expression Here" dialog to put my cursor where I want it.

Thanks!

This is a pretty quick suggestion:

I think that there are a lot of formulas that would be easier to write and maintain if a SQL-style BETWEEN operator was available.

Essentially, you could turn this:

ToNumber([Postal Code]) > 1000 AND ToNumber([Postal Code]) < 2500

Into this:

ToNumber([Postal Code]) BETWEEN 1000 AND 2500

That way, if you later had to modify the ToNumber([Postal Code]), you only have to maintain it once.  Its both aesthetically pleasing and more maintainable!

Hello Alteryx Devs - 

When I got to write some scripting in the formula tool, my data stream properties should be the first to be suggested once a user starts typing a letter, not the last. 

uppercase(Ad -> gives me:

DateTimeAdd

FileAddPaths

PadLeft

PadRight

ReadRegistryString

[Address]

I think we would need a dedicated R macro to ascertain the chances anyone in is going to need [ReadRegistryString] before they need a column of their own data that starts with [Ad...]

Easy fix.  Makes a big difference.  

Thanks.

Working in the accounting department, this has come up too many times now to ignore! 

Would LOVE LOVE LOVE to see a new formula available in the DateTime formula suite that mimics the function of the EOMONTH() formula when working with dates in Excel. 

The beauty of the EOMONTH() formula in Excel is that I can just give it a date, and then tell it how many months in the future or past I would like it to add/subtract... Alternatively, in Alteryx, this can require 2 or 3 nested DateTime functions to arrive at the same answer. 

Example: To find the end of the month 2 months in the future from today's date, I would use the following formula...

Excel = EOMONTH(Today(),2)

Alteryx = DateTimeAdd(DateTimeAdd(DateTimeTrim(DateTimeToday(),"month"),3,"months"),-1,"days")

Seems much more complicated than it needs to be in Alteryx, and easy to get lost in the nested formulas & non-intuitive adding/subtracting of months/days! I can see a new formula (something like DateTimeEOMonth?) being structured as follows in Alteryx: DateTimeEOMonth([Field],increment)

Please consider! Our accounting department thanks you heartily in advance...

Cheers,

NJ

// This is my new formula
MAX([Price] * [Quantity],0)
// This was my old formula
// [Price] * [Quantity]

Imagine being able to SELECT your text block (could be many lines) and right-clicking to see an option to Comment or Un-Comment those configuration statements.  I thought that you'd like it too.

Cheers,

Mark

Hello Alteryx Devs - 

When I got to write some scripting in the formula tool, my data stream properties should be the first to be suggested once a user starts typing a letter, not the last. 

uppercase(Ad -> gives me:

DateTimeAdd

FileAddPaths

PadLeft

PadRight

ReadRegistryString

[Address]

I think we would need a dedicated R macro to ascertain the chances anyone in is going to need [ReadRegistryString] before they need a column of their own data that starts with [Ad...]

Easy fix.  Makes a big difference.  

Thanks.

Hi Alteryx User and Alteryx Dev team,

I saw there are number of posts from the community asking for solution to calculate the NetWorkDays (e.g. similar to the networkdays in excel which to calculate the number of days different between the two days excluding weekend and holidays.)

Although we could build a macro for it, the performance is not ideal, especially when the data set is huge and/or the date range required is far apart from each other because there is currently NO a build-in function in Alteryx.  Alteryx will have to expand the date range by date and check whether each is a weekend or holiday.   It will an excellent idea if a build-in function for Networkdays could be built to minimize this hassle from everyone around the world. 

We are looking forward this idea could be take forward.

Thanks

Eric

Working in the accounting department, this has come up too many times now to ignore! 

Would LOVE LOVE LOVE to see a new formula available in the DateTime formula suite that mimics the function of the EOMONTH() formula when working with dates in Excel. 

The beauty of the EOMONTH() formula in Excel is that I can just give it a date, and then tell it how many months in the future or past I would like it to add/subtract... Alternatively, in Alteryx, this can require 2 or 3 nested DateTime functions to arrive at the same answer. 

Example: To find the end of the month 2 months in the future from today's date, I would use the following formula...

Excel = EOMONTH(Today(),2)

Alteryx = DateTimeAdd(DateTimeAdd(DateTimeTrim(DateTimeToday(),"month"),3,"months"),-1,"days")

Seems much more complicated than it needs to be in Alteryx, and easy to get lost in the nested formulas & non-intuitive adding/subtracting of months/days! I can see a new formula (something like DateTimeEOMonth?) being structured as follows in Alteryx: DateTimeEOMonth([Field],increment)

Please consider! Our accounting department thanks you heartily in advance...

Cheers,

NJ

use case : much of our users copy paste a formatted query to the Alteryx tools such as Connect in Db or Input Data (especially to reduce the data).

However, some of the formatting such a Carrier Return does not work

select 
 * from 
`re7_kerberos_za_e_*******_ee01pub_edd`.`v_*******_flux_20170511` 

Hi Alteryx User and Alteryx Dev team,

I saw there are number of posts from the community asking for solution to calculate the NetWorkDays (e.g. similar to the networkdays in excel which to calculate the number of days different between the two days excluding weekend and holidays.)

Although we could build a macro for it, the performance is not ideal, especially when the data set is huge and/or the date range required is far apart from each other because there is currently NO a build-in function in Alteryx.  Alteryx will have to expand the date range by date and check whether each is a weekend or holiday.   It will an excellent idea if a build-in function for Networkdays could be built to minimize this hassle from everyone around the world. 

We are looking forward this idea could be take forward.

Thanks

Eric

It would be awesome if there was a cross tab in DB option because right now I have to stream out millions of records to build a cross tab.

We're currently using Regex and text to columns to parse raw HTML as text into the appropriate format when web scraping, when a tool to at least parse tables would be hugely beneficial.

This functionality exists within Qlik so it would be nice to have this replicated in Alteryx.

Obviously, we need to retain the ability to scrape raw HTML, but automatically parsing data using the <td>, <th> and <tr> tags would be nice.

In the following page there is a table showing the states and territories of the US:

With Qlik, you can input the URL and it will return the available tables in tabular format:

As this functionality exists elsewhere it would be nice to incorporate this into Alteryx.

This is similar to a prior idea now marked complete "Allow macro metadata to persist until next run".  I tried the check box solution and still have the same issue, running V11.

What we NEED is for tools that derive columns like CrossTab to retain metadata from the most recent run and thus pass that metadata downstream for further tools and development.

Use case:

I have several cross tabs and before V11 I could run the flow once to push metadata downstream, then add or modify tools downstream and the derived fields from the cross tab stayed available in those tools to be recognized and referenced as I add more tools and logic. Now in V11 I am finding if I click on a tool or add a tool downstream the metadata for the derived columns disappears.

I attached pics to illustrate where I have 6 CrossTabs and decided I needed to add a summary downstream.  I had to run the flow to get metadata populated which is normal and I added the first summary, then inserted another summary and immediately the derived column metadata was lost in all paths after the crosstabs.  so ended up having to re-run the flow 5 more times for each summary tool added. then I had to re-run it 5 more times to adjust column names in selects after downstream joins.

I end up wasting a lot of time having to re-run a sufficient test file to feed all the variety of data necessary to generate all columns between most edits or new tool adds.  What used to take ~5 minutes to do now takes ~35

I recall seeing and discussing this issue previously and hoped the check box would resolve but It does not fix the issue.  

We see similar issue for tools downstream from other tools where the columns are derived or uncertain until that tool runs, such as, transpose, Joins and Unions.  I recall some discussion at user groups and in the community but the only reference I found this morning of seeming relevance is the one I mentioned above.