Pivot tables in Alteryx

Hi all,

I am trying to use Alteryx to replace Excel pivot tables. The data I am working with has a number of columns such as journal ID, account name and account code.

What I would ideally like to be able to do is filter the data using a select criteria. For example, I would like to filter all those accounts with an account name of revenue, that have a credit balance rather than debit and the other side of the entry also debits cash. However, i am having trouble doing so as when I use the filter tool it will only display the line that shows revenue it will not show the other side of the double entry so then I have no way of filtering the debit balance to show only those impacting cash.

Has anyone used alteryx for a similar scenario?

Thanks

Ellie

Best Practices

Accepted answers

ashissanpui

There are multiple ways to do that.

1. You can use table tool from reporting palette

2. You can use Cross tab from Transform palette

It's easy. Read the either one tool. You will be able to do that.

Ashis

Jean-Balteryx

@Elliecresswell ,

I attached a workflow that does the following :

- Clean the leading and trailing spaces

- Filters lines being "Cash", "WIP" or "Debtors"

- Joins both outputs of the filter

- Gets the right output of the Join Tool, the lines not having lines with "Cash", "WIP" or "Debtors"

Cheers,

Jean-Baptiste

Cash_WIP_Debtors.yxmd

All comments

Jean-Balteryx

Hi @Elliecresswell ,

Do you have a sample file to share to illustrate what you want ?

Cheers,

Jean-Baptiste

Elliecresswell

Hi, please see an attached example of the simplified data. The actual data is more than 1million lines.

So what I would like to be able to achieve is to use Alteryx to flag unusual postings. For example, in this case I would like to see all journals that credit revenue but then is not offset by a debit to cash, WIP or debtors. So journal 3 would be the one I would want it to flag up. But I am struggling to use the filter tool to capture all the lines of the journal as each one is made up of a few lines. So if there was any way to filter the whole journal eg 1,2 or 3 for a credit to revenue and then to anything except Cash, WIP or debtors.

Thanks

Ellie

Journal data example.xlsx

ashissanpui

There are multiple ways to do that.

1. You can use table tool from reporting palette

2. You can use Cross tab from Transform palette

It's easy. Read the either one tool. You will be able to do that.

Ashis

Elliecresswell

Thanks @ashissanpui and how would you add filters using the report tool?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703