Extracting Data using different text rules

[karizze_fitzgeraldine]

Hi All,

I have this set of data that I need to extract IDs from, it can be alphanumeric. Here's a sample:

Rule: The rule Alteryx needs to consider to extract the ID I need

Input: Sample of Input Data that I will be extracting data from, it can be dynamic and number of digits might change

Expected Output: the sample extracted data from the Input sample

This set of rules might change and expand. So I was thinking to keep a database that I can add more rules into and then Alteryx can read that and apply to the IDs if that rule is use etc, but I am confused how to do it. Regex? Formula? Find and Replace?

Rule	Input	Expected Output
NIL	11125685	11125685
Before first underscore (_)	242370611_s200760_f200796	242370611
After dash (-)	D-998344637	998344637
After second dot, before last dot (.)	0.3.3900356.1	3900356
Before dash (-)	4241-1	4241
After colon (:)	00OB7MM2F1NCSSNA296282:296282	296282
Before underscore (_)	182474_2-S	182474
After underscore (_), before last dot (.)	0.47.3_3902755.1	3902755

[Peachyco]

You might be able to accomplish this by dynamically generating a formula in the Formula Tool. In turn, you might have to use a Batch Macro to be able to do that, as demonstrated in this solved question.

I see this as nested IF-THEN statements: IF [Condition] THEN [Action].

1. Your database must have the individual [Condition] codes and the corresponding [Action] codes ready to go, written in such a way that the Formula Tool can understand them.
2. Read the codes into the main workflow, probably via the Input Data Tool.
3. Combine the codes into one IF-THEN formula. I'm thinking you can accomplish this with the Summarize Tool (Concatenate). You should end up with one data point that reads like "IF [Condition1] THEN [Action1] ELSEIF [Condition2] THEN [Action2]... ENDIF".
4. Pass that combo formula code to the Batch Macro, along with the dataset that needs to be modified.
5. The Batch Macro uses that combo formula to update the Formula Tool in it. Then, that Formula Tool applies the combo formula to the incoming dataset.

[karizze_fitzgeraldine]

Thank you, I will check and update this thread!

[karizze_fitzgeraldine]

hi, may I know if this can work with a dynamic replace?

[Peachyco]

@karizze_fitzgeraldine Are you thinking of modifying the formula in the Formula Tool using Dynamic Replace? I don't think that works because the Formula Tool will do its thing first before going to Dynamic Replace, at which point it's too late for Dynamic Replace to do anything.

The changes/update to the Formula Tool must happen dynamically before or while it executes, which, as far as I know, can only be done by the Control Parameter Tool and similar stuff under the Interface group of tools.