Formula Tool | Referencing Other Streams in Workflow

[JBO]

Can I reference another stream in my workflow using the Formula tool?

I would like to compare the values in a column from the L stream of a Join to the list of values from a summary of the J stream of the Join. If there is a match, I would like a "Y" put in a new column of the L stream. If no match, then an "N" put in the new column of the L stream. Please see attached. Below is my attempt at portraying the logic.

IF [L stream col] = [any of the values in J stream col] THEN "Y" ELSE "N" ENDIF

Disclaimer: I am fairly new to Alteryx. Go easy on me. Thanks.

[david_fetters · Accepted solution]

Hey JBO, for your purposes, the formula tool can only consider fields on the same row of your data stream.  Comparing your data stream fileds to results from a summary tool is not an uncommon practice if, say, you wanted to flag values over or under the mean.

To do this, either append or join (depending on whether there is only one row of summary data, or multiple rows grouped on some value) your summary data onto your other data stream using the Append or Join tool.  Then every row will have access to those fields and you can use the formula field as normal.  Alteryx will take care of making sure things get there on time.

Tip: Make sure the S input on the append tool has fewer records coming into it than the T input, so in this case it would likely be your summary tool outputting into the S input of the append tool.

Make sense?  Keep trucking, you're doing great!

Edit: I thought about it a little more, and in your use case you could take one of two approaches.

A)  You can append the summarized records from the J stream onto your L stream.  This will be a cartesian join where each row of your L stream will be duplicated so that every value of your summarized J stream is paired with every value of your L stream (e.g. 100 rows of L stream with 5 rows of J stream appended will create 500 total rows).  After that you can use the formula tool to create a flag (if L = J then 1 else 0 endif), and then sort on your L stream field ID asc/desc followed by sorting on the flag column descending.  Then use the sample tool to take the first record, grouping on your L stream field ID.

Cartesian joins can get big, however, so if you have a lot of summary records to compare through, i might go with option B.

B)  Join your L and J records on the field you want to compare.  Use a formula tool to set a field (say 'flag') to 1 on the results that come out of the inner join, then use another formula tool to set 'flag' = 0 on the original L records that fail to join.  Make sure you set your field selections in the join node so that both the L and J outputs have the same schema, then union the results after the formula nodes.  This should make sure that all of your original L records get a flag value set.

If this isn't clear, let me know and I can post an example of whatever approach you think makes sense!

[JBO]

David  

I am not sure why I didn't consider using Join -- I tried everything else. I don't think I need the flags at all. I just did a Join with L stream and J stream and then did a Union between the L and J streams of the new Join to bring it all back together again. Now I can create the formula as needed, though I may not even need it now.

Thanks much for the help!

Jen