Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Selecting rows between two string values

logiemeister
7 - Meteor

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

3 REPLIES 3
BenMoss
ACE Emeritus
ACE Emeritus

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

ponraj
13 - Pulsar

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

WorkflowWorkflow

 

logiemeister
7 - Meteor

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