Complex lookup to populate into excel form

[jermainelai]

Hi, 

I am new to Alteryx. 'Sheet 1' is the template that i have to fill yearly. Specifically, I would like Alteryx to auto populate the numbers B4:C6 and E4:E6 through summation of the source data in sheet 2. If both fields match, they should contribute to the sum in Sheet 1. For the fields that already have an input formula, I would not like alteryx to do anything to those because those are not in the input data (Sheet 2). Would appreciate all help! Thanks :) 

[grazitti_sapna]

Hi @jermainelai, try this workflow if it works for you?

Thanks!

[Kurohits]

Hi @jermainelai 

Another way of doing that. Not sue if you want to use Sheet 1 as a template and display data in the way it is showing in the sheet 1. Let me know if this solution looks fine with you. 

Thanks 

Rohit Gupta

[jermainelai]

Hi @grazitti_sapna , 

Thanks for the solution! Can i check if it will be possible for the end to be auto populated based on the headings without me having to specify every output cell [because the template is a lot longer]? And how can we automatically return 0 if the headers in the template cant be found in the source? Thanks so much!

[grazitti_sapna]

@jermainelai, as specified in your template my solution is specific to the template.

The Totals are calculated on the basis of the sum.

The First Total= Benfits+Temporary
The Second Total= Total+Test
The best way to do so would be to calculate everything in alteryx then use the template to overwrite the data.

Also, it will give you an error if you try to overwrite a formula field.

The reason why I Output the data in two different streams is so that the totals can be calculated as per the template specified and it was not possible to insert the value in the test column without the mentioned solution. 

In order to return 0 in case the headers do not exist in the source file but in the template would be creating a column and passing the value 0 manually and keep the order of the columns as it is in the template file as the data transformation from the source file will not contain the column names.

Maybe if you can provide better details of your use case or a sample file would help.

I hope it helps!

Thanks!

[grazitti_sapna]

@jermainelai, there is one way to fill the non-existing columns of the sources as zero in the template.

Considering your sample as the source, I have transposed the data to bring the headers into a data form, then I compared the data using join. For the fields that were successfully joined I used a formula tool to get the values from the right side and for the non-matched values that are L output I hardcoded the values as 0 and then unionized the data to crosstab and then the final output.

But as mentioned in the above post you will have to map the columns exactly as they are in the template and remove any formula fields which need to be calculated in the alteryx.

Let me know if that helps!

Thanks!