Alteryx Designer Discussions

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

Select records with dynamic range?

Highlighted
8 - Asteroid

Hello,

 

I am using the Batch Input Macro from this post (https://community.alteryx.com/t5/Alteryx-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480) to input one tab from 36 Workbooks. The tab is not the exact same in each workbook, hence the need for the batch macro. 

 

I want to modify the Macro to look down the rows of the tab, & to select only those records that occur below a specified piece of text...

 

For example:

  • in Workbook 123, the text "Select from here" appears in row 134, therefore I want the "Select Records" tool with the range 135+
  • in Workbook 456, the text "Select from here" appears in row 160, therefore I want the "Select Records" tool with the range 161+

I want the modification to be made before the Macro Output tool like in the below screenshot where I added the "Select Records" tool

 

Any ideas?

 

Alteryx select records.JPG

Highlighted
Alteryx Partner

I would just add a new field called "indicator" with the MultiRow Formula Tool that looks something like:

 

IF CONTAINS([Field1], 'select from here')

THEN 1

ELSE [Row-1:indicator]

ENDIF

 

Then, you can perform a filter on indicator=1, and just remove the first row. (or do IF CONTAINS([Row-1:Field1], 'select from here).

 

The important bit here is that you configure the MultiRow Formula Tool to set "Values for Fields that don't Exist" to "0 or Empty", so that everything except for the desired rows will be 0.

 

Let me know if this helps or I've missed something,

 

Cheers!

Highlighted
8 - Asteroid

Thanks very much for your help!

Highlighted
Alteryx Partner

No problem, glad I could point you in the right direction!

Highlighted
5 - Atom

Hi, I found this solution useful but I got stuck in another one.

I have attached a workflow excel where I only wanted to select the records/ mark the indicator to 1 from "start from here" to "end here", which are being repetitive in the sheet.

 

I have tried by using another Multi row formula where using IF ELSE statement to achieve this but couldn't help much.

 

appreciate any help in this regards. 

Labels