We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Contains plus Previous Row

bsheremeta
7 - Meteor

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.

3 REPLIES 3
Luke_C
14 - Magnetar

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.

 

Luke_C_0-1626810712836.png

 

JoaoLeiteV
9 - Comet

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.

 

JoaoLeiteV_0-1626810924868.png

 

Hope this helps!

 

EliB
9 - Comet

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:

1.JPG

 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!

Labels