Filter Account Pairing Plus Interface Tool

[Gamaliel]

Hello Great Alteryx Users,

I am seeking help with this one. I want to filter JE Identifiers which have debits (positive in net amount) for asset (from the account type column) and credits (negative in net amount) for expense. E.g. 001 should be filtered because it has a debit to asset and a credit to expense.

Furthermore, please also help me in connecting an interface tool to it that will give me more choices (e.g. debit to liability and credit to revenue). 

IN addition, I do not know if I made the proper workflow in the attached app. Thanks 

Please see attached.

[atcodedog05]

Hi @Gamaliel 

Here is how you can do it.

Workflow:

1. Using formula tool I am categorizing transition type as debit if the net amount was positive and credit if the net amount was negative.

2. Conacting Transation Type and Account Type to get options. This will also be loaded as options in list box.

3. Filtering the options in filter tool.

4. Using select tool to keep only needed columns.

Hope this helps : )

[Gamaliel]

Hello @atcodedog05 , thanks on this. Amazing. It is going there. In the picture below, JE identifier 005 was included in the filter. What should be included is JE identifier which has both a debit to an asset and a credit to expense. In the case of 005, though it has a debit to asset still its credit is not an expense but a revenue. Hence, only 001 shall be filtered since it has both the asset debit and credit to expense.

[atcodedog05 · Accepted solution]

Hi @Gamaliel 

Here is the workflow. I have added a block to check does the identifier have all selected options.

Workflow:

Hope this helps : )

[Gamaliel]

@atcodedog05 Thanks on this. Perfect 🙂 

Btw I'll add another question. I forgot to include it above. Hats-off to your expertise.

[Gamaliel]

Hello @atcodedog05 

Can I have a follow up question?

Yes, JE 001 shall be filtered since it has a debit to asset and credit to expense. However, I want to capture all of the line items included/with JE 001 as identifier. For e.g. below, I want to include in the filter the line item for liability since it is also part of 001. So the goal is to capture all JE Identifier with debit to asset and credit to expense plus all of the attached entries to it.

This will enable us to analyze the nature of the whole transaction.

Thank you.

[atcodedog05 · Accepted solution]

Hi @Gamaliel 

Here is the workflow. I have modified the connection to get all rows of identifier.

Workflow:

Hope this helps : )

[Gamaliel]

Thanks again @atcodedog05 . One last question, before I close this issue:

1. I want to set a threshold above absolute amount of 5,000 for those JE debit asset and credit expense. Please note that the threshold shall only apply to debit asset and credit expense.

2. I added an interface tool for the amount filter in the attached app below.

3. However, those which will be filtered are those only above 5,000.

4. Same with the prior issue, the line item for liability JE001 must be filtered also (even if it is below 5,000). What is important is that both debit to asset and credit to expense is 5000 or above. The other attached entries must be filtered regardless of their amounts so long as they are part of JE001.

Thank you @atcodedog05 

[atcodedog05 · Accepted solution]

Hi @Gamaliel,

Please find the workflow with the logic.

Hope this helps : )

[Gamaliel]

Thank you so much @atcodedog05 . I do not know how you are doing it. I will study the logic.