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

Getting N number of rows below a certain header

crunyeon
7 - Meteor

Hi Everyone, 

 

I'm currently working on parsing out pieces of a messy Excel file and would like to get a list of deliverables under a heading.  

 

The problem is, the deliverables heading is always in the same column but not always the same row, and each deliverables list varies in length.  The deliverables sections are, however, always preceded by a NULL row and end with a NULL row.  So the data looks like:

 

null
Deliverables:
Deliverable 1
Deliverable 2
null

 

I would like to write a formula that looks for the "Deliverables" heading and marks each deliverable until it hits a null row.  So by the time the formula is finished, it looks like:

 

nullFalse
Deliverables:False
Deliverable 1

True

Deliverable 2True
nullFalse

 

Any help would be greatly appreciated.  Thank you!

3 REPLIES 3
danrh
13 - Pulsar

You should be able to use a Multi-Row Formula tool.  If every heading is directly preceded by a null value, use this formula:  !(isnull([YourData]) or isnull([Row-1:YourData])).  This will show true for all of your deliverable records.  Note that if your FIRST record is also a heading, you'll need to select NULL from the drop-down for "Values for Rows that don't Exist."

 

image.png

 

 

Hope it helps!

Kenda
16 - Nebula
16 - Nebula

Hey @crunyeon! I think you are looking for the Multi-Row Formula tool. Try to add a new bool field with the following formula:

 

if [Row-1:Field1]="Deliverables:" || ([Row-1:NewField]=1 && !isnull([Field1]))then 1 else 0 endif
crunyeon
7 - Meteor

Both solutions worked perfectly.  Thank you!

Labels