Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

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

Lookup the data based on the Reporting date

RichardAlt
8 - Asteroid

Sorry guys, This question is not about designer in alteryx.

 

This question is formula in excel. After I run the workflow it will overwrite the sheet in template. After that I need to lookup the data to Main File.

 

I want my lookup to be dynamic. Every month the data change. For ex. I have data in January, In the month of Feb. data will change.

 

This is my Main Sheet.

RichardAlt_3-1592447282184.png

 

 

 

This is my Lookup sheet, Every time I run the workflow the data will change.

RichardAlt_5-1592447311834.png

 

So I have data in lookup sheet for the month of Feb. I want to dynamically lookup the data based on the month(Reporting Date) and column. The output something like this,

 

 

This is the output what I want,

RichardAlt_4-1592447297908.png

 

I want something like If Condition, If the reporting date is equal to 2\29\2020 then lookup the data and post to Main File row of February-20 and If Condition, If the reporting date is equal to 3\31\2020 then lookup the data and post to Main File row of March-20

 

Thank you!

 

 

4 REPLIES 4
T_Willins
14 - Magnetar
14 - Magnetar

Hi @RichardAlt,

 

Actually, I would recommend a combination Alteryx and Excel solution to this.  Before outputting the data to your workbook either change your reporting date to match your Main Sheet's format or create an additional field with the date in the same format.  For instance for 02/29/2020 use the formula "%B-%y" which will return the result "February-20".  If you put this field before your Lookup Sheet data you can use either a vlookup or index-match formula on your Main Sheet.

 

Looking at how this is configured, though, if your Alteryx output is only a single month, your January-20 data will be zero when you run February-20's data unless you are hard coding (i.e. - copy/paste special values) each month after running the Alteryx workflow.

Laurap1228
11 - Bolide

Something like the attached should work.

 

You will need to Copy and Paste as Values on your Output Tab before the Alteryx Output overwrites with the next months numbers. 

RichardAlt
8 - Asteroid

Hi @Laurap1228, Thank you!

 

What if I changed my output to Append instead of Overwrite? So if the sheet has Feb data for ex. and I run again the workflow it will append only the March and in that case the data in Feb and March are available in main sheet. Is that possible? That the lookup is in wide range? for ex. I have Feb 29 and March 31 data both must be lookup. Sorry for my bad English.

Laurap1228
11 - Bolide

Append to Existing sheet should solve that problem.

Labels