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.

Alteryx Analytical App passing '_' when encountering empty spaces for column values

5 - Atom

Team,

Kindly help me out here. I have been trying to solve for this app problem. 

My requirement is to show users a list box of customer names they want the data output for. The dates and level of granularity work fine but when it comes to List box passing the parameters to Filter tool it uses an underscore '_' instead of the wide or empty space in the column name and this is causing my filter tool to miss out on most of the values. 

For instance, BDI LOGISTICS LLC  is BDI_LOGISTICS_LLC and the data stream does not recognize this new input from the list box tool interface. 

Can anyone help me on this? 

Highlighted
11 - Bolide

Could you post your workflow please. 

Highlighted
5 - Atom

Drew, I just did. Apologies for missing out on this earlier. 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

You can replace those underscore characters with spaces by setting your Action (27) tool to "Update Value with Formula" and using the following expression:

 

'[CUSTOMER_NAME] IN ('+RegEx_Replace([#1],"_"," ")+')'

 

This will build the same IN expression using the user-input values and replaces "_" with " ".

Highlighted
Alteryx
Alteryx

Within the filter tool, use a replace formula to replace the "_" with a space (" "). See attached for an example. 

 

echuong1_0-1582058567002.png

 

Highlighted
5 - Atom

Charlier, 

Thanks for the solution. Matter of fact I did try to use those but '_' is not limited to spaces, Interface tool(list box) passes everything which is a non char as '_'

and my column values had more than spaces. It is working for now. 

Highlighted
11 - Bolide

@farazuddin - one way to handle the issue with spaces and punctuation being replaced by underscores is to make a copy of the customer name and use that to rename the columns. However, make sure to see my second point about when the interface tools are populated.

 

How to fix column headers if the field has spaces/punctuation:

  1. In your summarize tool, group by CUSTOMER_NAME twice (make two copies of the name)
  2. In your Crosstab tool, put one CUSTOMER_NAME as the column header, and one as the column values. The result will be the original name in the first row, and the modified name (with underscores) in the header.
  3. Use a Dynamic Rename tool to rename the columns using the first row. Now, your column headings have the appropriate spaces/punctuation.kelly_gilbert_1-1583012239920.png

 

IMPORTANT: app interface tools (like the drop-down) populate BEFORE the workflow runs. So, when the drop-down is presented to the user, the database input and crosstab haven't run yet. The drop-down won't show the user fresh results from the SQL Server; it will show them the results from the last time the workflow ran.

 

If you want to pull a fresh list from the database each time the user runs the app, then you can use a chained app (I've attached a simple example):

  1. App #1 pulls the list of customers from the database, then outputs them to a .yxdb. Once that's complete, it will move on to App #2.
  2. App #2 uses the .yxdb to populate the drop-down (this would be your existing app)

In App #1, go to View --> Interface Designer

  1. Click the gear icon.
  2. Check On Success - Run Another Analytic App, then navigate to app #2. Make sure the path is relative (it doesn't have the full C:\... path)
  3. Un-check On Success - Show Results to User
    1.png

  4. Click the layout icon
  5. Add a label telling the user to click Continue. When they press continue, it will pull the names from the database, and then move on to app #2 where it will display the drop-down to the user.2.png
Highlighted
5 - Atom

Thank you so much. I will try this approach. The best thing about Alteryx is one thing can be done in many ways. 

 

Labels