community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Selecting rows between two string values

Meteoroid

Hi all,

 

I'm trying to work out how to split the following rows between the "##############################################" markers in the table below into separate streams.

 

  • There are about ten separate reports/tables in a single csv export.
  • The sample is likely to change quite often, which means that the Select Rows tool won't work. 
  • I found a query that is somewhat similar HERE, however, the worklow is very specific to the problem posted.

 

[F1][F2][F3][F4]
##############################################[Null][Null][Null]
# Freeform Table[Null][Null][Null]
##############################################[Null][Null][Null]
[Null]VisitsUnique Visitors[Null]
Page44924170[Null]
Page44924170[Null]
[Null][Null][Null][Null]
[Null][Null][Null][Null]
##############################################[Null][Null][Null]
# Freeform Table (2)[Null][Null][Null]
##############################################[Null][Null][Null]
[Null]VisitsUnique Visitors[Null]
Day44924170[Null]
26/08/20183535[Null]
27/08/2018414393[Null]
28/08/2018766728[Null]
29/08/201813121262[Null]
30/08/201815261480[Null]
31/08/2018440430[Null]
[Null][Null][Null][Null]
##############################################[Null][Null][Null]

 

I've trawled the community, but can't find a solution. Can anyone help with this or point me in the right direction?

 

Chris

Alteryx Certified Partner
Alteryx Certified Partner

Here is how I would come to a solution around this.

 

Firstly, I would create a new column, this column will only be populated for the rows which match a table name structure. This can be done with a regex formula...

 

 

IF REGEX_Match([F1],"# .+")
THEN [F1] 
ELSE NULL() 
ENDIF

 

 

Lets call this new column 'Table Name'.

 

I then use the multirow formula trick commonly used to perform a 'fill down'.

 

This leaves me with a table which looks something like...

 

2018-08-31_08-01-15.png

 

Then we just need to filter out the useless rows which again can be done with a regex filter.

 

I've attached an example workflow for you.

 

Ben

Quasar

Here is the sample workflow for your case.  Hope this is helpful. 

workflow.PNGWorkflow

 

Meteoroid

Thanks for the elegant solution - regex was perfect for this use case. 

 

@ponraj - yours also got me to the finish line, so I've starred yours too. Thank you


Chris

 

 

Labels