Creating a report showing "balance != 0" from journal entries

[dannybass]

Apologies in advance.  This is probably no brainer stuff, but I am brand new to Alteryx.  I am basically exporting a data dump from our accounting journals.  My end result goal will be to show journal accounting details for control numbers that do not balance to 0, sorted by control number.

My progress so far is as follows:

- filtering [journal] and [detail desc]

- sorting by [control]

- filtering duplicate data [control] - had to use a custom macro from this board in order to get this to work, since the built in unique tool was not filtering truly duplicate data.

- summarize, group by [control], and sum [posting amt] to create a new column field [balance]

This is where I am stuck.  I am not sure the best way to go about this.  My end result should be to remove all the posting detail that has a 0 balance, and keep all the posting detail that != 0.

Thanks in advance for your help.

[mceleavey]

Hi @dannybass ,

You just need to apply a filter and take the True (T) output:

As for the other things, the unique tool does filter truly duplicate data, but depends on the fields you select.

M.

[dannybass]

I tried the above in my earlier experimenting (I should have mentioned this, my apologies). When I ran it, it just removed the zero balances that were my [balance] = 0 lines (essentially the subtotal rows).  I assumed this was corrected since [balance] = null on the rows that had the details.

[mceleavey]

@dannybass ,

I don't understand what you're trying to achieve.

Yes, if you don't want zero rows, just use the filter to remove them.

Null does not equal zero, so it won't remove nulls.

So...what is the question?

M.

[dannybass]

Question would be how do I get the balance from the subtotal rows, to populate the balance field in the various acct details rows so that I could use the filter you suggested above?  I guess that would be the easiest solution?

I included a picture, hope this makes sense.

[mceleavey]

ah, ok, can you post your data so I can run the workflow?

[dannybass]

OK just carved out a sample with two control#s.  One should have a zero balance, they other should not.  They are both duplicate control numbers.

[mceleavey · Accepted solution]

Hi @dannybass ,

I think you just need to change the union to be a join which will be joined on Control, if I'm understanding correctly:

M.

[dannybass]

Thank you so much, that worked.  Appreciate your help.  /smackshead so simple 😃

[mceleavey]

@dannybass it's only simple when you know the answer 🙂