Remove duplicate values in a column based on date in another column

I have a fee that is charged once a month. I am trying to have the fee only show up one time in a month, then have the rest of the cells blank.

Here is what I have right now.

I'm also very new to Alteryx so if you have a solution, please don't leave out anything you may think is obvious, because I am still learning.

Thank you!

Developer

Best Practices

Data Investigation

Accepted answers

kathleenmonks

Hi @johnsa64,

If you are ok with removing the other rows, then @DataNath solution will solve your problem. If you wish to keep those rows, then you can union your results back together and keep the rows that aren't unique. See my attached workflow and screenshot. I have added an ID column just to replicate your other columns and the fact that they may have different data (ex. Trans_ID)

unique.yxmd

All comments

DataNath

@johnsa64 if you bring on a Unique tool and tick [Invoice Month] under 'Columns' in the configuration, that ought to get you what you want. The unique tool basically just looks for the first instance of each unique value in the selected field (or first unique combination if selecting multiple fields), then discards everything else as duplicates (these come out of the bottom (D) anchor. What you want should come out of the top (U) anchor. Here's a small example:

Before:

After:

kathleenmonks

Hi @johnsa64,

unique.yxmd

DataNath

Good call @kathleenmonks - had overlooked that they only wanted to 'blank' duplicates, rather than remove the entire rows! For that, I'd just use a Multi-Row Formula though, based on the changing month:

In @johnsa64's case, the Multi-Row Formula would be set to update existing field (Gatekeeper Fee) & the expression would be:

if [Invoice Month]=[Row-1:Invoice Month] then null() else [Gatekeeper Fee] endif

For the sake of explanation, all this is saying is: If the Invoice Month in the current row is the same as the one in the previous row, null that value. If not, show it.

kathleenmonks

@DataNath nice! Much more streamlined approach. I saw "unique" and got tunnel vision.

johnsa64

This worked and my output in Alteryx looked right.

However, now I am having an issue in Tableau where its only populating SOME of the unique values. Its odd.

January, February and May populate

March, April, June do not.

I know this isn't the same program, so I will keep after it. Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703