How to avoid formulas/tools breaking due to "missing cell"

Hi all, big shout out to this community as several of you have helped me tremendously. I have a beautiful workflow that @Maskell_Rascal has helped me build (essentially built it for me) and I have one last follow up question before it is complete.

This is an example of what my matrix looks like before it goes through the workflow. Several of the tools and formulas reference cell A1 and the catch is that cell A1 can be different for the end user. It could be DB02, DB04 etc. in which case all of the formulas would break and trigger "missing" for all the tools.

How do I make the workflow rename or ignore that cell so that the end user does not have to modify formulas or tool inputs. It can change to anything and does not have to reflect its original value at the end.

Again huge thanks to Maskell and others who have helped me. I've never found a community more willing to help people learn.

Workflow

Input

Tips and Tricks

Accepted answers

Maskell_Rascal

Hey @alexcellings

With a couple modifications, you can make the workflow non-dependent on the headers.

First make sure that your Input tool is configured to treat the first row as data like this:

Then we'll add a Sample tool to skip the first row, and the rest of the workflow will work as needed after updating the selections/formulas.

Attached is an updated workflow.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Check Modules Concat Results Ver 3.yxzp

Maskell_Rascal

Hey @alexcellings

I just noticed in that last workflow I sent that since we are skipping line one, it screwed up our downstream formula for concatenating the results with the header. I added a multi-row formula tool to this new version that corrects the problem.

Version 3 Results:

You can see that the combined Results field says APF23,JR instead of AP13,JR

Version 4 Results:

Sorry about the screw up there.

Attached is the updated workflow.

Thanks!

Phil

Check Modules Concat Results Ver 4.yxzp

All comments

lnguyen

are you wanting to make the change directly back to this excel file minus cell A1? if you are pulling in the value for cell A1 as the constant for other calculation done in alteryx, it should not matter what those value would be. Unless the value is null then you would need a conditional respond in case of null built in. I am confused as what exactly that you want to do with this. Perhaps attach the workflow next time?

Qiu

@alexcellings

if this cell A1 is the column name, how about the use a select tool to rename it to a fixed name.

alexcellings

See attached example workflow. Obviously I cannot attach the real thing for confidentiality reasons but if you'd like to see what I am saying, change the value in cell A1 to DB04 or anything you want and notice how the transpose tool and formula tool no longer work before they are looking for that old original value.

Basically I am wondering if it's possible to have either 1) a dynamic formula & transpose tool that won't be dependent on that cell being exactly the same. or 2) Is there a way to replace that cell with something so that the tools and formulas will work regardless of which "DB03, DB04" is uploaded?

Check Modules Concat Results Ver 2 (1).yxzp

alexcellings

Yes it is a column header and I had the same thought initially and that (select tool rename) is the first thing I tried.

Unfortunately what happens is when you upload the next Matrix file with a different column header in cell A1 the select tool no longer can find the old "Example DB03" column header to rename to our fixed "Database" value and so it doesn't rename anything.

Maskell_Rascal

Hey @alexcellings

With a couple modifications, you can make the workflow non-dependent on the headers.

First make sure that your Input tool is configured to treat the first row as data like this:

Then we'll add a Sample tool to skip the first row, and the rest of the workflow will work as needed after updating the selections/formulas.

Attached is an updated workflow.

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Check Modules Concat Results Ver 3.yxzp

alexcellings

I had no idea the transpose tool did not require that header. Man you should charge for this service you are amazing! Can't thank you enough.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235