Modifying a dataset with conditional delete and auto filling of empty cells

Dear valued community,

Continuing my first steps in my Alteryx Journey I would like to request your help on a) conditional deleting values and b) on auto filling of empty cells.

I attached a dummy database (the real contains more rows) and will explain what I want to do.

Step a)

* For every row that has a value in "Omschrijving" but not in "Aantal" I would like to delete the value "Totaal 3"

* For every row that does not have a value in "Code", "Omschrijving", "Aantal", and "Drijver" I also would like to delete the value in "Totaal 3"

Step b)

* For every blank cell after a 6 digit code in "Code" I would like to add for each row a unique identifier and only when "Aantal" has a value and except for the row prior to a new 6 digit code in "Code"

How the database looks now

* https://imgur.com/a/3PHHYpA

How the example would look like (based on Excel) with in yellow the proposed changes

* https://imgur.com/a/5oJDAVk

Many thanks in advance for anyone who can help me out here and warm regards from the NL!
CT

Detail database_v1.2_alteryx.yxmd

Detail database_v1.2_alteryx.xlsx

Output

Best Practices

Transformation

Accepted answers

MattBSlalom

The screenshots of source and target data were very helpful to fully understand the data rules you described, but the attachments only included the target data file, so I generated a source file to test my workflow against. It appears your target data may have an error on the 101020-1 row. I believe the Totaal3 value should not have been deleted:

For your "Step a", we just need to update a field based on other values in the same row, so a standard Formula tool can be used. I wrote two formulas in the same tool, but you could achieve the same result splitting the 2nd formula into it's own tool, or adding the complexity all together in a single formula.

"Step b" was the more interesting challenge. I used a couple of Multi-Row Formula tools to get the logic worked out. The first tool assigns the "group" or "parent" code to each subsequent record. The 2nd Multi-Row Formula tool handles all the cases you outlined as to whether the unique identifier should be generated. If the identifier should be generated and the previous row has a code suffix assigned, then add 1 to that value, else start with 1 on the current row. Finally there's a standard Formula tool to concatenate the resulting fields from the 2 Multi-Row Formula tools (the code group and the generated code suffix where one exists). The end has a select tool to remove the temporary code group and suffix fields.

Generate codes with multi row fomulas.yxmd

All comments

MattBSlalom

Generate codes with multi row fomulas.yxmd

ChrisT1

Dear MattBSlalom,

Thanks a lot for your swift and very explanatory response! I used your workflow on the bigger database and received the right results. Next to that, I would like to thank you for you elaborate response on why and how you choose certain fields. this is great for my learning curve!

All the best and warm regards,

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703