For each tile value, choose certain rows

[Philly]

Hi everyone--

I've been trying on my own to get something working for a little while now, to no avail. I hope it's not too much of an imposition to ask for a little help here.

I have a dataset, which I have tiled and sorted by date, a typical row of which looks like this:

. . . blah blah blah // DATE // VALUE // . . . blah blah blah . . . // TILE_NUM

VALUE is an integer running from 0 to 9.

What I'd like to do is: Within each TILE_NUM, pull out all the pairs of rows such that the first row has VALUE=3 and the second row has VALUE=7.  It's important to do this by TILE_NUM, because if I don't, I could get a pair like:

. . . // 2018-05-11 // 3 // . . . // 44

. . . // 2015-07-07 // 7 // . . . // 66

which I do not want.  All the pairs should share the same TILE_NUM, and ascend by date.

Any help is very much appreciated.  Thanks!

[MarqueeCrew]

Could you try a replacement of | for //?

Replace([Text],"//",'|')

Then you might use the text to columns tool and change the delimiter to |

This might be the "easiest" way to do what you're looking to do (without RegEx).

Cheers,

Mark

[Philly]

I'm sorry, I think I was not clear in the original post.  The "//" do not appear in the data.  I used them in my post only to represent delimitation between fields.  I am not trying to change any formatting; rather, I want to pull out certain rows, as described.

Thanks again.

[ThizViz · Accepted solution]

Off the top of my head, I would first assign a record ID to preserve sorting.

Next, us a filter tool to pull out all records with either 3 or 7 as the value.

Making sure the record IDs are properly sorted, use a multi-row formula to flag all the 7's where there's a 3 in [RecordID]-1, then do the same (use a separate column for the flag) where the value is 3 with a 7 in [RecordID]+1.

I don't have access to Alteryx at the moment but this should get you on your way. If you still need help, message me and I can give a more thorough response.