community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Lookup of Value to the First row

Asteroid

Hi Team,

 I am trying to convert a excel VBA macro in to alteryx..I am stuck at a point.Can you guys help me.

I have a two excel file as input for Alteryx as in  below screenshot.

 

Input 1

 

 

input1.png

 

Input 2

input2.png

I want an output as in input 1 Sales Fcts column where in the first row has value from input 2 and the remaining corresponding business unit has  0's .PFA highlighted in red color.

How do I achieve this in alteryx?.

 

OR

 

Also is there any way to achieve the below format .ie Generating row no for Business unit key.The row no column has to restart from 1 when the value in Column C changes.

 

Output1.png

 

Any help would be really appreciated.

 

Regards,

Haya

Magnetar
Magnetar

You can do either one! Here are solutions for both options, you can take your pick...

 

OPTION 1: 

1. Use Find & Replace tool to lookup the Business Unit in Input 1 and match it to the Business Unit in Input 2. When it finds a match, append the results from the Sales Fcst column in Input 2. 

2. Since this first step will fill in that value for every row instead of just the first, you can use a Multi-Row tool to check for changes in Business Unit and then only keep the first one.

 

OPTION 2:

1. Use the Multi-Row tool and group by Business Unit, then just use the formula [Row-1:Row N]+1 to create a record ID in each Business Unit group.

 

Hope that helps! 

 

Cheers,

NJ

 

Labels