Contains plus Previous Row

Question

Hello, I have the following data in the column txt

"5

16.

342

234

134

34

13

55."

I need to filter through the data and obtain the following: (so basically every row before the data with the period)

"5

16.

13

55."

I use Multi-row formula and type as an expression:

IIF(CONTAINS([txt],"."), [Row-1:txt] , Null())

But not getting correct results.

Eli_B · Answer

Hi!

It's Alteryx & there is a thousand ways to do any one thing, so I'm sure there is a statement you could use to accomplish this, I'm just not sure what it is haha. If you are waiting for feedback to accomplish something though, the way I would attack this is below:

I would use the Record ID tool on your input data, then a filter to grab any of the records that contain the decimal, then use a Formula tool on those records, creating a new ID field where you take the records record id & subtract 1. You can then use this new ID to match to the other records that fell out of the first filter. I added a sort to give you your original order back & then used the Select to drop the Record ID field. Hope this helps!

Example.yxmd

JoaoLeiteV · Answer

Hello @bsheremeta,

You can use a Formula tool to add a column that'll check if you should get the data or not. First, create the column with a "YES" or any other word if the data contains a period. Then, using a multi-row formula, check if the next row has a period, if it does, also set it as a "YES". Finally, just filter the data.

Hope this helps!

example.yxmd

Luke_C · Answer

Hi @bsheremeta

Here's an approach you could use with the multirow formula tool, essentially I used the tool to identify instances where the value had a period, or the row below had a period and flagged them. Then from there I just filtered on the flag.

Ignore the first sample tool, I had to put some text in the text input tool because the numbers kept defaulting to numeric data types and losing the decimal.

New Workflow1.yxmd