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

Action: Update value with Formula Not working

ChrisSpr
8 - Asteroid

ChristopherS_0-1584641117814.png

So here's the brief: This isn't changing the filename.

 

Here's the long of it:

I have a single excel workbook with a sheet for each day of the month plus two at the end that i don't care about.

I need to build a csv file with the data from the range B22 to C25 from each day.  

I tried to do a dynamic input solution (Pull in the sheet names then go through a Dynamic input tool) but, despite visual evidence to the contrary, the tool errors saying the schema is different on each page.

 

Right now I'm just trying to pull yesterday's data and that will be the main function going forward. Run once a day and pull yesterday's data appending to the sheet.  

 

The issue is I can't seem to get this to function.  It does nothing.

 

Can someone help? 

Thanks in advance.

8 REPLIES 8
CharlieS
17 - Castor
17 - Castor

Hi @ChrisSpr 

 

In your Action tool configuration, make sure you set the Action target correctly by highlighting the "File - value=" line of the Input tool. The highlighted field will determine where the Action formula will go into effect. 

 

20200319-ActionTarget.PNG

ChrisSpr
8 - Asteroid

Thank you for replying.  I am indeed highlighting that portion, however, nothing seems to change.

It still just outputs the first page instead of the portion ( B22:C25) of sheet Today - 1 (18) 

Rather maddening 🙂

 

ChristopherS_0-1584645262704.png

 

danilang
19 - Altair
19 - Altair

Hi @ChrisSpr 

 

What you've created is a batch macro with the control parameter used to pass in the date your looking for.  When you run the macro workflow directly in Designer, the control parameter and the corresponding action tool do not get executed.  It's only when you take your macro and insert it into a calling workflow the the action tool will work.  

 

The expression in your action tool doesn't actually use the value of the control parameter.  It calculates the day before today.  You can replace the parameter and action tool with Text box coupled with a dynamic input like the attached. 

 

w.png

 

The Dynamic input tool is configured like this to replace the entire path of the Input Data Source Template from "text.xlsx" to the value of the [destination] field.

 

di.png

 

This will read the values from the previous day's worksheet. 

 

Dan

  

ChrisSpr
8 - Asteroid

This is wonderful!

 

Anyway to set it so it passes "the first row has data" or should i just bump it up a row to B21?

 

Either way it's great! Thank you!

ChristopherS_0-1584721453875.gif

 

ChrisSpr
8 - Asteroid

@danilang  Since you were so helpful maybe you could help with my next/final issue maybe?

 

My next step is to iterate back through the previous pages and append it to a file.  A backfill of sorts.

I was going to modify my previous attempt with a counter but since I'm not understanding how macros work maybe you can brief me on it?

I have workbooks ranging back to 2014! 

 

Thank you so much for the previous help I do appreciate it, and for any help you give going forward.

 

 

danilang
19 - Altair
19 - Altair

Hi @ChrisSpr 

 

Click on the Edit button in the Dynamic Input config.  This opens a window which is almost identical to the one in the Input Tool.

 

di2.png

 

Set any options you need, like First Row Contains Data and click OK.  The options you set here will apply every time the workflow runs.

 

Dan

danilang
19 - Altair
19 - Altair

Hi @ChrisSpr 

 

What do you mean by iterate back?  Do you want to run this process for all the sheets from T-1 back to the beginning of month?  Is there any thing that you need update on sheet T-2 that you calculate on sheet T-1 or are the sheets independent

 

Dan

ChrisSpr
8 - Asteroid

@danilang 

All sheets are independent.  

I just wanna grab b22:c25 on sheets 1 through 31 (or whatever end of the month is) and append it to a file.   I only need to do this once and going forward it'll all just be "get yesterday's data"  

 

I'll need to transpose it so column 1 is the row names and column 2 is the data but that's another issue.

Labels