I am having a problem where my data set has (null) values, however none of the tools seems to be able to resolve the problem completely as the data columns are subject to constant change. I have tried "Data Cleanse", "Multi-Field Formula" and "Dynamic Replace", none are able to give a robust solution. Can someone suggest a solution? I have expanded on the issue below.
I have a batch macro that runs data through for usage of software licenses over a given time period. This time period is subject to change depending on the time in which we are interested in. The macro output will give something similar to below, showing in a given month how many simultaneous license usage events there were and at what frequency, see table below.
| License Type | Month | Number in Use | Frequency |
| Type 1 | Sep-18 | 1 | 28 |
| Type 1 | Sep-18 | 2 | 26 |
| Type 1 | Sep-18 | 3 | 45 |
| Type 1 | Sep-18 | 4 | 23 |
| Type 1 | Sep-18 | 7 | 15 |
| Type 1 | Sep-18 | 8 | 10 |
| Type 1 | Sep-18 | 10 | 8 |
| Type 1 | Sep-18 | 12 | 5 |
| Type 1 | Sep-18 | 13 | 2 |
| Type 1 | Sep-18 | 14 | 2 |
| Type 1 | Oct-18 | 1 | 63 |
| Type 1 | Oct-18 | 2 | 56 |
| Type 1 | Oct-18 | 3 | 47 |
| Type 1 | Oct-18 | 4 | 35 |
| Type 1 | Oct-18 | 5 | 20 |
| Type 1 | Oct-18 | 6 | 15 |
| Type 1 | Oct-18 | 7 | 12 |
| Type 1 | Oct-18 | 9 | 9 |
| Type 1 | Oct-18 | 11 | 8 |
I then use a "Cross Tab" tool to reconfigure this data to show Months along the top and show frequency against count, see table below.
| License Type | Number in Use | Sep-18 | Oct-18 |
| Type 1 | 1 | 28 | 63 |
| Type 1 | 2 | 26 | 56 |
| Type 1 | 3 | 45 | 47 |
| Type 1 | 4 | 23 | 35 |
| Type 1 | 5 | (null) | 20 |
| Type 1 | 6 | (null) | 15 |
| Type 1 | 7 | 15 | 12 |
| Type 1 | 8 | 10 | (null) |
| Type 1 | 9 | (null) | 9 |
| Type 1 | 10 | 8 | (null) |
| Type 1 | 11 | (null) | 8 |
| Type 1 | 12 | 5 | (null) |
| Type 1 | 13 | 2 | (null) |
| Type 1 | 14 | 2 | (null) |
As you can see, when the table is generated any missing values are populated with (null). Ordinarily, this is not a problem as I know (null) values can be easily removed using the tools I mentioned above. However, if I was to change my time period to look at July-August, the tool I used to remove the (null)'s does not identify the new column's automatically. Likewise, when data for new months is pulled in, in this case November will shortly be available, it too will not be recognised by the tool without manual input. I wish to replace all (null) with 0.
Is there a way I can get around this? Is there a tool in Alteryx that can be set to replace all (null) values in a dataset automatically without specifying target Fields?
