In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

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

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 1
1.xlsxFruit: Orange
2.xlsxFruit - Apple
3.xlsxFruit - Apple
4.xlsxFruit: Mango
5.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:

FileNumber
10
21
31
40
51

 

But currently my output is:

FileNumber
11
21
31
4Null
5

Null

 

Is there a better way to do it?

 

Thanks!

4 REPLIES 4
IraWatt
17 - Castor
17 - Castor

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.

 

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
17 - Castor
17 - Castor

Sorry slight issue @lifeofaworkingadult 

IraWatt_0-1656776757901.png

 

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:

IraWatt_1-1656776772235.png

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

 

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.

Labels
Top Solution Authors