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

Dynamic Data Cleansing where the columns are changing

mwindeler
7 - Meteor

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 TypeMonthNumber in UseFrequency
Type 1Sep-18128
Type 1Sep-18226
Type 1Sep-18345
Type 1Sep-18423
Type 1Sep-18715
Type 1Sep-18810
Type 1Sep-18108
Type 1Sep-18125
Type 1Sep-18132
Type 1Sep-18142
Type 1Oct-18163
Type 1Oct-18256
Type 1Oct-18347
Type 1Oct-18435
Type 1Oct-18520
Type 1Oct-18615
Type 1Oct-18712
Type 1Oct-1899
Type 1Oct-18118

 

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 TypeNumber in UseSep-18Oct-18
Type 112863
Type 122656
Type 134547
Type 142335
Type 15(null)20
Type 16(null)15
Type 171512
Type 1810(null)
Type 19(null)9
Type 1108(null)
Type 111(null)8
Type 1125(null)
Type 1132(null)
Type 1142(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?

5 REPLIES 5
NickC
Alteryx Alumni (Retired)

Hello,

 

I managed to solve this using a multi row formula.  As you know that all the new columns are going to be 'Numeric' you can tick all and make sure you select 'Dynamic or Unknown Fields', this will ensure that when filed names change they are going to continue being selected.

 

IF Isnull([_CurrentField_]) Then 0 Else [_CurrentField_] ENDIF

 

Nick

mwindeler
7 - Meteor

Thanks Nick, this has resolved it. I had been trying this method but had not selected 'All', I had ticked the 'Dynamic or Unknown Fields' box only. I am glad it was a simple fix!

AudieCruz
7 - Meteor

Thank you, I was able to use this. Ended up using the multi field formula for it, though. Maybe it was a mistype, but I was not able to find dynamic fields on the multi row formula. 

MRapp
7 - Meteor

Not sure if something changed in Alteryx since then, but isn't it Multi-Field-Formula? I didn't see the option in Multi-Row-Formula to select "dynamic or unknown fields".

NickC
Alteryx Alumni (Retired)

Typo - Its the Multi Field Formula. 

 

 

Labels