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!
Solved! Go to Solution.
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
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.
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.