ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Interface Tools: Update Value with Formula Issue

ripcityscorp15
7 - Meteor

Hi All,

 
Here is my current scenario:
 
I have an excel file with multiple tabs, all with different schema. I am currently trying to combine these tabs into one output from Alteryx.
 
My current workflow looks like this:
 Main WorkflowMain Workflow
1. Input has "LIST ALL SHEET NAMES" selected, & Ouput File Name as Field = "Full Path"
2. Text to column tool splits out The File Name and the Sheet Name as such:
  • FileName: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx|||`2018 Budget$`
  • FileName1: C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx`
  • FileName2:  ||List of Sheet Names>
3. Select Tool: Changes the Name of "FileName1" to "File Name"
 
4. MACRO TOOL:
  • Macro WorkflowMacro Workflow
  • Currently My Macro Tool Looks like this:
    • The First Action Tool is "Update Value (Default)" 
      • I select the box "Replace a specific string" and then remove the  "||List of Sheet Names>" so all I have remaining is the Filename1 value listed previously.
    • The Second Action Tool is "Update Value (Default)" 
      • I select the box "Replace a specific string" and then remove the  "C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx||" & " S' " so all I have remaining is the First Tab Name.
      • This will have same Tab name as the Tab selected in the Input File Listed below.
    • Input File: Choose the First Tab of the excel file
      • (It can be any tab, but easiest to just choose the First. Also, this Tab name will be the one listed in the 2nd action tool previously mentioned)
RESULT: When I currently do all of this -- the workflow works as I imagined. Pulling all the different excel tabs into one Alteryx table that can then be manipulated. 
 
HOWEVER! I do not want future users of the tool to have to go into the Macro, and have to manually manipulate the "Replace a Specific String" in the Action Tools in order for it to work. 
 
Therefore, I then used "Update Value with Formula". It is my understanding that the same string of data would flow into the Action Tool (aka the FileName listed above), and that using a formula would be able to get me tot he same result I had been manually typing on the First and Second Action Tool. 
 
As a result, here are the two formulas I currently am using on the action tools in the Macro:
  • First Action Tool:
    • Substring([#1],0,Length(Left([#1],FindString([#1], "|"))))
    • This should get me the result:
      • C:\Users\zszykowny006\Documents\Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx 
  • Second Action Tool:
    • Substring([#1],Length(Left([#1],FindString([#1],"|")+1)),
      (Length(Left([#1], FindString([#1], "$")))
      -Length(Left([#1],FindString([#1],"|")+1))))
       
    • This should get me the name of my first tab:
      • 2018 Budget 
When I run the workflow, though, with the Macro using these formulas I get this error:
  • Error: New MULTIPLE TABS BATCH MACRO_V2 (79): Record #1: Tool #11: File not found "C:\Users\zszykowny006\Documents\2018 Budget"

I have confidence these Formulas above are correct after testing them in a different workflow using the formula tool.

 

So my question is:
  • Why does the full file name not come through? The problem seems to be with my First Action Tool formula...As it only only pulls through " C:\Users\zszykowny006\Documents\" and is missing "Wiffle Ball\2018\2018 Bats for Brains Budget.xlsx"
  • If the full file name comes through, I feel like this should work the same way as when I was manually manipulating the Update Value (Default) option.

I have spent many a hour on this, so any help would be truly appreciated!

 

Thanks!

patrick_digan
16 - Nebula
16 - Nebula

@ripcityscorp15 I will have a look at why your version is not working. In the meantime, I've attached the way I would do it using reference shortcuts. I find these much easier to work with.

patrick_digan
16 - Nebula
16 - Nebula

@ripcityscorp15 You have 2 action tools, and the second one is completely overwriting everything that the first one is doing. I would connect both of your control parameters to 1 action tool. Perhaps I'm missing something, but this simpler formula seemed to work with your dataset (where #1 is the file path and #2 is the sheet name):

Capture.PNG

For future reference, you can use the field [Destination] in the formula to use what's already in the tool. The action tool is the 1 case where you can use data coming in as well as the data that's already in your tool to build your formula. In this particular case, i think it's easiest to just use 1 action tool. For an example, there are times where I use a function like:

Replace([Destination],"text to replace", DateTimeParse([#1],"%m/%d/%Y"))
ripcityscorp15
7 - Meteor

Incredibly helpful! There was very little, via search, I could find in the Community, giving good detail about this specific functionality of the Action Tool. Thank you for taking the time to explain it all out!

MatteoFedrizzi
5 - Atom

Great tip!!! Thanks a lot @patrick_digan, this has helped me fix a similar issue.

Cheers!

Labels