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

Select records with dynamic range?

mb1824
9 - Comet

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

9 REPLIES 9
tcroberts
12 - Quasar

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!

mb1824
9 - Comet

Thanks very much for your help!

tcroberts
12 - Quasar

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

Vipin1
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. 

tolis
6 - Meteoroid

Did someone found a solution on this one?

tolis
6 - Meteoroid

If we want the range to be dynamic and want to select specific range of data based on a condition, what then?

apathetichell
18 - Pollux

Hi @tolis no idea what happened to the author from last year - perhaps they started a new thread? Do you have a workflow with a problem in a batch macro situation where you want to start counting records from a specific spot? If so - can you post more information - maybe a data and error sample?

 

My gut feeling is the problem with the most recent posters workflow was that they had the record start signifier MULTIPLE TIMES in the same worksheet. This would cause the multi-row formula to reset and do all sorts of funky things that it wasn't designed to do. The multi-row was designed for one starting point and then filtering based on that one starting point.

 

did you try @tcroberts 's solution? Just to be clear the range was static - everything was brought into Alteryx but only the records matching the condition were sent out of the macro.

 

 

 

tolis
6 - Meteoroid

I posted my issue and what I am trying to achieve on the community. Follow the link below @apathetichell 

 

Thanks

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/HOW-TO-REPLICATE-THE-SAME-WORKFLOW-FOR...

apathetichell
18 - Pollux

@tolis - thanks. The more information you can give about your exact specifications (sample data/sample workflow/etc) the easier it will be for someone to give you a solution. Everything you are asking about can be done - but describing to you how to do it can be cumbersome and incorrect depending upon specifics. If you post a sample on that thread and explain it someone will be able to generate something usable and more specific to your case.

Labels