Importing a .yxmd as a .csv to recognize if a Formula is Update current/ Create new column

[hungdm]

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/yxmd-as-csv-Formula-tool-Identify-if-a...

I have I topic related to this case, but the old solution did not work.

How can I define if one Formula Tool in a Node ID performs:

a. Update to current column 

b. OR create new column.

Can anyone help me? Thanks for reading!

[hungdm]

Anyone has an idea, please kindly show me :-ss

[danilang]

hi @hungdm 

1. Have you tried to modify @Jonathan-Sherman's original solution?  If so post the results here so we can look at what you've done.

Dan

[hungdm]

hi @danilang 

The solution did not work as what I expected. It could not differentiate the option: Update current column or Create new column.

Please see the attached workflow. The current solution marked all the following formula as Create new column:

<FormulaField expression="[a]" field="b" size="4" type="Int32" />
<FormulaField expression="a*2" field="b" size="4" type="Int32" />
<FormulaField expression="[b]+1" field="b" size="4" type="Int32" />

[danilang]

Hi @hungdm 

@Jonathan-Sherman's is probably the best that you can hope for without putting in an enormous amount of work.  What it does is compare any fields that are input fields with any fields that are formula fields, flagging any fields that are in a Formula but not in an Input as new.  

Here's the entire xml that defines your first formula tool

    <Node ToolID="2">
      <GuiSettings Plugin="AlteryxBasePluginsGui.Formula.Formula">
        <Position x="174" y="54" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <FormulaFields>
            <FormulaField expression="[a]" field="b" size="4" type="Int32" />
            <FormulaField expression="a*2" field="b" size="4" type="Int32" />
          </FormulaFields>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText><![CDATA[b = [a]
b = a*2
]]></DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxFormula" />
    </Node>

 The two rows that correspond to the 2 actual formulas start with "FormulaField expression="

<FormulaFields>
    <FormulaField expression="[a]" field="b" size="4" type="Int32" />
    <FormulaField expression="a*2" field="b" size="4" type="Int32" />
</FormulaFields>

In your workflow, the first line creates a new field [b] and the second line changes the existing field b.  So ideally, you'd want to see [a] line as new and the a*2 line as a modification.   However, if you look back at the entire formula tool xml, there's nothing that says what the input fields are in the tool.  The 2 lines don't have any indication that a field is new or not. 

In order be able to separate out these cases, the workflow would have to start at the input tools, evaluate the changes made in each subsequent tool and build up a list of the input metadata at each tool.  This will difficult enough for simple tools like an input tool or a formula tool, but in order to track the metadata changes through a cross tab, the algorithm would have to "run" the cross tab since the outgoing fields depend on incoming data.

This overall analysis is similar to the process that Alteryx Designer uses to keep metadata current while you're developing a workflow.   Like I said at the start, an enormous amount of work.

Dan