A little help with "dynamic" style formulas?

[jrobiso2]

I have a formula tool with a bunch of repetitive formulas.

For each field in the left side of the join:

i.e. If [RIGHT_DATE] > [DATE] THEN [RIGHT_FIELD] ELSE [FIELD]

Currently I have a formula for each field in the right side of the JOIN.

If I get more fields added, I would have to manually add the formula for that field, which is tedious and due to this being put up on an Alteryx Server, takes a week due to process.

I'd like to be able to kind of do a Dynamic formula, where 

Left field value, even if it didn't previously exist:

If [RIGHT_DATE] > [DATE] THEN [RIGHT_new_FIELD] ELSE [new_FIELD]

Anyone have any ideas?

[braveraj]

Just a thought. I approached it by transposing your fields but grouping by your dates. Then split the results from where you want only the right values and combine with where you only want the left.

Hope this helps!

Adam

Edit: Added in RecordID tool for distinct grouping on crosstab and transpose tools.

[patrick_digan]

@jrobiso2 My multi-row -field -column macro was designed for this exact use case! You would set it up like this:

I've manually selected Field1 and Field2 an example. You can make it truly dynamic by using the dynamic field selection or manual fields to exclude (thus any new fields would be unchecked and would be included).

Then in the Expression window, I typed: 

If [Right_Date] > [Date] THEN [Right__CurrentField_] ELSE [_CurrentField_] endif

The key is it allows [Right__CurrentField_] (Notice that's 2 underscores after Right). The multi-field tool included with alteryx uses the [_CurrentField_] syntax, and my macro expands on that by allowing you to add the Right_ part (thus [Right__CurrentField_]). For each field selected, it translates this (for example [Right_Field1] and Right_Field2] in my example). The attached word doc is included in my example macro linked above, but I'm including it here as well. It explains the macro in more detail. It takes a little time to understand, but the expression window in this macro can reference fields in unique ways (for example, if the names aren't consistent but you know the column will always be 1 to the right, you can user [_CurrentField_:Column+1])

Edit: You can also use the dynamic replace tool out of the box (see attached example):

[Ben_H · Accepted solution]

Hi @jrobiso2,

Is the only test that Right Date > Date?

If so you just do something super simple like this -

It just filters on date, where right date is higher dynamic select to turn off the left fields, and keep the right - then dynamic rename to remove "Right_". For rows where Right Date is not greater than Date it just keeps the original fields, dynamic select is used to turn off any "Right_" fields.

Regards,

Ben

[jrobiso2]

I'll give this a test. Thanks!

[jrobiso2]

I'll give this a test and see what happens ;-)

[jrobiso2]

While these were all great, I find @Ben_H solution to be the simplest and most elegant. Thanks everyone!!