Multi Row Formula?

[bsolove]

In the sample data attached below, I would like to know if I can do the following.

1.  Always remove the first instance where the Form Name Field is Sch20cB.

2.  Create a field were I assign a record ID to each record, starting with the record that has a value in the SUB EIN field, and changes when it hits the next record when there is a value in the SUB EIN field.  I created a column in the sample data for the desired end result.  Right now I would have 5 records with the same record ID, but that may not always be the case.  There could be more or less

Thanks in advance!.

[grossal · Accepted solution]

Hi @bsolove,

here is a way to do this:

What happens:

1) Adding a Record ID to preserve the row order

2) Filtering for "Sch20cb"

3) Skipping the first row (Sample Tool)

4) Union everything back together

5) Sort them to the original order

6) Multi-Row Formula to get the custom count

Workflow attached. Let me know if I got it right.

Best 

Alex

[AbhilashR · Accepted solution]

Hi @bsolove, I use the approach of join to remove the first instance of the Form Name Field 'Sch20cB', and then a combination of multi-row formula and tile tool to get the sequence right. Does this answer your question? Please mark this post as solution accepted if it does. 

[grossal]

Interesting solution @AbhilashR.

I think you should have done it without the Tile Tool and just configured the Multi-Row Formula with numbers instead of text, but it's great reminder to me to check out the Tile tool again and hopefully bring it into my default tools at one point.

[AbhilashR]

@grossal - you caught me there, ha! My first build did use a pure multi-row approach, but ever since I saw this post by @DavidP on Tile Tool (thank you David), I have been forcing myself to go more native Alteryx.