How to delete all succeeding rows after a specific row in Alteryx

[Junnel_8533]

Good-day! Would just like to know if there is a way to delete all the rows after a specific row in Alteryx. In what I am currently working on, I want to remove all the rows after the row with subtotal Row# 492 (Row#493 onwards to be removed). The row with the subtotal may also change for instance it may be in Row #310 next time I run the workflow, hence I would need to remove Rows 311 onwards. Thank-you very much!

[DavidSta]

Hi @Junnel_8533,

you can achieve this with a "Multi-Row-Formula" Tool.

I recommend creating a new field e.g. "Delete Flag".

You set the value of this column to true if your previous row contains the value you are looking for - in your case "SUBTOTAL" - or if the previous row was already marked to be deleted.

Then you can use a Filter remove the unwanted rows based on the "Delete Flag" column.

Please take a look to the attached workflow.

Best regards,

David

[Junnel_8533 · Accepted solution]

Hi @DavidSta ,

Thank-you very much for your response, I am relatively new to Alteryx, and would just like to understand who you came up with this expression below. Thank-you very much!

[sparksun · Accepted solution]

If all the rows after the Subtotal row only have Null value,then you can just use Data Cleansing tool.

[DavidSta · Accepted solution]

Hi @Junnel_8533,

there is not much more to explain on the logic from the formula than from my initial post.

If you want to generally understand the Multi-Row Tool and how it work I can recommend to check out this Interactive Lesson.

The recommendation from @sparksun is great as well if it's about "keeping all rows instead of the null ones".

[Junnel_8533]

@DavidSta 

Hi DavidSta,

Just an additional inquiry on your expression, would just like to ask what the highlighted portion of the expression means? Are we testing if [Row-1 : Delete Flag] = "SUBTOTAL" since we have an "OR" as the operator? If yes, why did Alteryx return the value as "TRUE" under Row #8 instead of "FALSE" (Row#7 Delete Flag Column is not Equal to "Subtotal")? Thank-you very much for your help on this :)

[DavidSta]

Hi @Junnel_8533,

When you find such a statement in a logical expression this simply indicates "True".  So this means "[Row-1 : Delete Flag]" is the short version of "[Row-1 : Delete Flag] = 1"

The "Row-1" indicates that the expression is looking at the previous row.

Based on the screenshot let's think about following:

The current calculation is happening in Row 7. 

We want to know if the previous Row (Row 6) is the one where we find "SUBTOTAL" or if the previous row was marked as deleted (True). In this case we find "SUBTOTAL" and with this  set the value of the Delete Flag to True.

Now going to the next row 8

We don't find SUBTOTAL, but when looking at the previous row (Row 7) we find, that the Delete Flag was set to True, so our conditional statement is true as well and we continue setting the value for Delete Flag for the current row to True as well.