How to generate a 0value when a string of text is not found in a files using batch macro?

Question

Hi all, I am new to Alteryx, and it's my first time creating a workflow. Hope if you guys could help a guy out.

I am using batch macro to run multiple excel files into my workflow. My question is this:

Is there any way to input a 0 value when a specific string of text is not found in the file?

Background:

- 5 files, each with only 1 sheet - Called Sheet 1 (with different schemas).

- The text that I am looking for (e.g. Apple) in Sheet 1 is located in different parts of each of the respective file.

FilesSheet 11.xlsxFruit: Orange2.xlsxFruit - Apple3.xlsxFruit - Apple4.xlsxFruit: Mango5.xlsxFruit: Apple

My problem: I've managed to filter "Apple" using 'Filter' tool, but I am having a problem in indicating a "0" value when the "Apple" is not found, e.g. in this case would be in File 1 and 4.

I would want my expected output to be:

FileNumber1021314051

But currently my output is:

FileNumber1121314Null5Null

Is there a better way to do it?

Thanks!

lifeofaworkingadult · Answer

It's a little hard for me to generate sample data. The macro is the one as attached above in the link in the first post.

My main problem is that I am unable to generate a 0 value when the data does not have a particular string of text. Because as you can see, I am filtering based on the string of text I am looking for (e.g. Apple). Hence, the filter tool only filters out data from Files 2, 3, and 5, but not Files 1 and 4.

I was thinking of using the count tool when the particular string does not exist in the file (e.g. File 1). But when I batch macro load, the count shows 3. So that didn't work out either.

IraWatt · Answer

Sorry slight issue @lifeofaworkingadult

If you want to share a workflow with the data and macros you need to go options-> export workflow then tick all the things to add:

Also you will need to attach the data separately as the directory tool doesn't link to any dataset, unlike the data input tool.

lifeofaworkingadult · Answer

Here's a sample of my workflow.

So basically, the idea is to get the cost of apples from the 5 source files in Excel. The Excel source files are basically conversions of PDF files, so hence, the schema for each files are different.

Test Workflow.yxmc