Pivot tables in Alteryx

[Elliecresswell]

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

[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

[ashissanpui · Accepted solution]

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 · Accepted solution]

@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

[Elliecresswell]

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

[Elliecresswell]

Thanks very much, that is great @Jean-Balteryx 

[Jean-Balteryx]

@Elliecresswellno problem, I'm glad it helps you.

Don't hesitate to accept as solution so people can access the solution too.

Cheers,

Jean-Baptiste