Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Counting all Types including empty ones

HM
8 - Asteroid

Hi,

 

I'm trying to show all the ovedue types and count the number of items for those types even if they're 0 or empty. Unfortunately, when I use the Summarize node, the overdue types without values are excluded. Example below:

 

Input:

Overdue Type Item ID
Not Overdue a
Overdue  
Not Overdue b
Not Overdue c

 

Current Output (grouped by overdue type and count using countdistinct):

Overdue Type # of Items
Not Overdue 2

 

Desired Output:

Overdue Type # of Items
Not Overdue 2
Overdue 0

 

Thanks

5 REPLIES 5
AndrewW
11 - Bolide

Hi

 

I'm going to assume you've made an error with your request and the expected results are:

 

Overdue Type# of Items
Not Overdue3
Overdue0

 

Although I can't replicate your results, using the Summarise tool gives me:

 

Overdue Type# of Items
Not Overdue3
Overdue1

 

There may be very simple ways around counting the Null, but I don't know them so have approached this in a slightly different way. First I have added a sort, sorting by the Overdue Type and ItemId. Next I added a Multi-Row formula, adding a new field, to convert Nulls to 0 and to put a 1 against each unique Item Id - hence why the sorting is important. The multi row formula expression is: IF IsNull([ItemId]) THEN 0 ELSEIF [ItemId] = [Row-1:ItemId] THEN 0 ELSE 1 ENDIF.

 

If it's not a distinct count of ItemId that's required then a multi-row formula isn't required, a standard formula could be used to convert all nulls to 0 and all non-nulls to 1 and sum that column.

 

The results of the count distinct, using the ItemId and Summing the new field are:

 

 Overdue TypeCountDistinct ItemId Sum New Field 
 Not Overdue 3 3
 Overduw 1 0

 

Workflow is attached and fingers crossed I haven't misinterpreted your requirements.

MarqueeCrew
20 - Arcturus
20 - Arcturus

In your example, you have all Overdue Types in the input data.  I took an approach to collect the unique types (regardless of counts/items) and used that information to set a default of 0 for each type.  I'll then count the non-empty occurrences of item ID's and add them to zero.  All Types will appear along with their count.

 

Capture.PNG

I hope that this helps you.  

 

 

See you at inspire16,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
HM
8 - Asteroid

Thank you all for attempting to solve my problem.

 

Unfortunately, I forgot to mention that because there is an extra layer of complexity (i.e. projects). For each project there are multiple items, some of which are overdue and some which are not overdue. For Project 2, both the overdue and not overdue types will show since there are items in those types, however, for project 1, there are only items in the not overdue type so if I summarize it by project then by type, the overdue type will not show for project 1 but I do need all the types to show regardless of whether there are items which fall into those types.

 

Apologies for leaving that piece of information out and I appreciate any help I can get.

 

Input:

Project NameTypeItem ID
1Not Overduea
1Not Overdueb
2Not Overduec
2Overdued

 

Current Output (grouped by overdue type and count using countdistinct):

Project NameOverdue Type# of Items
1Not Overdue2
2Not Overdue1
2Overdue1

 

Desired Output:

Project NameOverdue Type# of Items
1Not Overdue2
1Overdue0
2Not Overdue1
2Overdue1

 

 

Thanks

AndrewW
11 - Bolide

Having multiple projects does change things slightly and MarqueeCrews approach is required, creating all combinations of Project Name and Overdue Type and filling in the gaps with the actual Item counts where available, converting those unavailable (NULL) to 0. Attached workflow does this. Sadly I can't put screen shots into this forum as my local IT have locked down this functionality with this forum software. 

HM
8 - Asteroid

Thanks Andrew! It workedSmiley Happy

Labels