How to generate a 0value when a string of text is not found in a files using batch macro?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
