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

[lifeofaworkingadult]

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.

Files	Sheet 1
1.xlsx	Fruit: Orange
2.xlsx	Fruit - Apple
3.xlsx	Fruit - Apple
4.xlsx	Fruit: Mango
5.xlsx	Fruit: 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:

File	Number
1	0
2	1
3	1
4	0
5	1

But currently my output is:

File	Number
1	1
2	1
3	1
4	Null
5	Null

Is there a better way to do it?

Thanks!

[IraWatt]

Hey @lifeofaworkingadult,

Do you have any example data or workflows you could share? Your method of using a batch macro seems good. Not sure why your getting 1 in file 1 and NULL's in 4&5.

[lifeofaworkingadult]

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.

[IraWatt]

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]

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.