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

Alteryx designer Discussions

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

How to pull x amount of rows out of the middle of a dataset?

Atom

I have a 1000 row excel workbook with multiple sections on the same sheet, each with a unique header. Each section has exactly 16 rows of data included. I want to pull the rows only from one specific header called "wages".

 

I tried using a multi-row formula and the below expression to pull only these 16 rows but it isnt working. Any ideas?

 

IF Contains([F1], "Wages", 1)

THEN [Row+1:Test]+1 & THEN [Row+2:Test]+1 & THEN [Row+3:Test]+1 & (... all the way to +16)

ELSE 0

ENDIF

Meteor

You could use a formula to add in another field when the column header contains 'Wages', then a multi-row formula to start once the 'Wages' field is located, to carry down the 'Wages' annotation until another header is located (or a counter until 16 is reached); then filter out everything that doesn't have the new 'Wages'/counter annotations.

 

Would that work for you by chance?

Alteryx
Alteryx

@qqqalt You can probably just use the formula tool and create a new column with this formula

 

If contains([F1], 'Wages') then [F2] else null() endif

 

This will just grab all the wages and pull them out. 

 

You can also use a filter tool with the contains([F1], 'Wages') to filter out anything that's not wages. 

Digan
Alteryx
Highlighted
Alteryx Certified Partner

Hi @qqqalt 

 

Try something like this... Use the Multi-Row formula tool to categorize each row by seeing if the current row has a value in column F1. if it does, it will copy that value into a new field this creates call "section". on rows where there's no value found, it copies the "section" value from the previous row... essentially filling down until a new a new value is found. Afterwards, use a Filter tool to only keep the rows filled with "wages".

jrgo_0-1572654265683.png

 

Here's the expression in the MR formula tool.

IF ISEMPTY([F1])
THEN [Row-1:section]
ELSE [F1]
ENDIF



Jimmy
Teknion Data Solutions

Labels