Issue with tables

[jpolomsky]

Hi all,

I am faced with an issue which I assume there is a simple solution for. 

I am working with payable days.My year end is 31.12.2021 and I have a file which shows due dates of invoices, lets say there are 4 invoices.

Invoice 1 - 31.10.2021 - Amount 500

Invoice 2 - 31.12.2022 - Amount 500

Invoice 3 - 31.12.2025 - Amount 500

Invoice 4 - 31.12.4785 - Amount 500

Essentially, I want this due date to be compared against the year end to which I am working on and calculate how many days are left or overdue. So in the first case it would be 60 days overdue. (This part I can do)

Depending on these days, I would select these invoices and place them into 4 different groups (This part I can also do)

Group 1 - Overdue invoices (Invoice 1) - Amount 500 EUR in aggregate

Group 2 - Payable within 1 year (Invoice 2)-Amount 500 EUR in aggregate

Group 3 - Payable within 5 years (Invoice 3)- Amount 500 EUR in aggregate

Group 4 - Payable within more than 5 years (Invoice 4)-Amount 500 EUR in aggregate

However issue occurs here. Since I want to used this group categories to create a table, I need each of these groups to be in that table, but in most of the data sets, payable within 5 years and payable within more than 5 years will not be present. No inovices will fall into that category. It would most likely look like this:

Group 1 - Overdue invoices (x amount of invoices) - Amount 100,000 EUR in aggregate

Group 2 - Payable within 1 year (x amount of invoices) - Amount 500,000 EUR in aggregate

Group 3 - Payable within 5 years (0 invoices ) - Amount 0 EUR in aggregate

Group 4 - Payable within more than 5 years (0 Invoices) - Amount 0 EUR in aggregate

What do i do in this case? Meaning, I will have 0 invoices in the group 3-4 for example, however I need my table to understand that even though we do not have any invoices which say this, the table will still show this category with 0. 

My process is that after this is done, based on certain parametres, I use cross tab to create a table, which is where the group 3-4 would not happen because it would not be in the data set. Any ideas how to still make this table? 

1. I was thinking about inputting this table into workflow as a blank and filling out the numbers I have using the workflow I would create and the parts which would not any balance would simply turn to 0, however this requires another input and I would much rather not do that.

Thank you 

[csmith11 · Accepted solution]

Sharing a snap shot of your workflow would help us better answer the question.

But what it sounds like you want to produce a result for the last two groups, even when there are no records that fall into that category.

If you add the COUNT RECORD tool into a blank Alteryx canvas and right Click it->Click Open Macro: You'll see Alteryx takes a similar approach to what you outlined in your question.

A Text input tool can be leveraged as you mentioned above to ensure the existence of each category.

I've put together a quick example. (See attached) This process likely needs to happen before your crosstab tool.

Please let me know is this helps. Consider marking as the solution to help other community members identify the solution faster.