FINDSTRING batch macro between two sources - MATCH AND APPEND

Hi,
I have a text file which has been split into rows and I have a second file with specific keywords I want to match in the first file and append it to the output. The output should only include the matched rows.

For example:

Row 1 : " Products delivered on Jan 07, 2017"

My keyword file would have values such as: "Jan, Feb, Mar, etc".

I want to include a findstring operation as I will need the character number further in the module.

The ideal output would be:
Text matched_on_Ch (result from the FINDSTRING( str, target) operation )

" Products delivered on Jan 07, 2017" 23

I have already performed this task with an append field and findstring after that; however, I am specifically looking into performing this task using a macro in order to avoid the huge multiplication of rows due to the append tool.

Would really appreciate your help with this!

Thank you in advance

Denise

flow.PNG

Workflow

Input

Regex

Tips and Tricks

Macros

Dynamic Processing

Interface Tools

Accepted answers

patrick_digan

@Riccid Attached is a batch macro version. I've included three different ways of passing data from the control parameter to the formula tool. I prefer the method on the left. Let me know if you have questions!

Find Replace Example.yxzp

patrick_digan

I was wondering if location should not be >0 rather than >1 ?

The findstring function is 0 based, so if your field was "Jan oaeuoeu.a" and looked for Jan, the findstring function would return 0. If it doesn't find it, it returns -1. So I usually define it as >-1.

The first method, on the left, does not have to be actually connected to the parameter control? However, if I isolate it from the rest of the macro and use it separately, then it doesn't work in my flow as it does not show the tab "questions" at all and there is no output.

Not having to connect interface tools has been game changing for me. As for trying to isolate it and use it elsewhere, there are a couple points: 1) I created a batch macro as you can see on the workflow configuration tab of the macro. Control Paramaters will only work in batch macros. 2) If you did try to copy the control paramater to another batch macro, you will have to rename it on the workflow configuration tab. You can see on the configuration tab that I named it match:

If I copy it to a new batch macro, it get's renamed:

To rename it, you can click on the tool, then the annotations tab, then rename it from Control Parameter (2) to Match:

That should do the trick.

How would you isolate the 3 methods, particularly, I am curious to understand how would the first one work (on the left).

You would need the macro in and the control parameter for all 3 methods. I've added a comment box to the picture below to show you which pieces are needed for which:

For testing/debugging purposes, methods 1 and 2 allow you to try out different values by putting sample data in the workflow configuration tab and clicking the play button with the macro open. In the first picture above, you can see that I'm testing the value "Jan". The macro will work without a test value, but you can use test values during testing/debugging. Test values will not affect the value when the macro is actually used in a workflow.

Also, what do these values in the formula actually do: [#1], '%Question.ControlParam.Match%'.

the [#1] is using the data in the formula tool from a connected interface tool. Note that I connected the control parameter directly to the Q of the formula tool. check here and here. Essentially, the data from the connected tool name #1 (Alteryx's default name) is passed directly from the control parameter into the formula tool.

the %Question.ControlParam.Match% is using the data in the formula tool from the interface tool named Match. This can be used almost anywhere, and without actually connecting to the tool. I believe it's called a "reference shortcut". I love the warning in the help documents: "Be cautious when using this method, as it may not always produce expected results." I use this method all the time except in the rare case where I have to use an action tool. Note that you sometimes have to put it inside quotes when using it just like I did in this example.

All comments

patrick_digan

@Riccid Any chance the find replace tool can help? I've attached a quick example. I don't know the full context of your situation, but it seems that you should be able to feed in your data on the left and then your keywords on the right. You could then use findstring function after that.

Find Replace Example.yxmd

Riccid

Hi Patrick,
Thanks, that is also a solution. however, I would like to see this task performed in a macro as I could apply it to other scenarios too.
I just dont seem to get a grip with batch macros! 😀

Thanks for your help,
Regards,
Denise

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703