Free Trial

Alteryx Designer Desktop Discussions

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

Dynamic Data Cleansing - without cleansing one column

maygross
8 - Asteroid

Hi,

 

I'm trying to cleanse data after it goes through the crosstab tool. I have a formula to find a month over month delta and when the value is null, the delta doesn't calculate properly, so I use a data cleansing tool after the crosstab. My issue is that my column names change every month (dates) so I always have to update the data cleansing tool.  I know that I can fix this through using the multifield tool (in the screenshot below) but I'm wondering if there's any way I can tell alteryx to not cleanse one of my columns. Like is there a formula I can make to cleanse all my date columns but not change anything in my first column (customer name)? Can I add something to this formula as an exclusion?

maygross_0-1699990334951.png

 

4 REPLIES 4
CoG
14 - Magnetar

I'm a little confused still, so here are a few ideas:

  1. Uncheck the box "Field 1" or "Field 2" and the Multi-Field tool will leave it alone.
  2. If Field 1/2 correspond to Name/Value pairs from Transpose Tool (or similar principle), You can filter on the Name to separate out everything that is a customer name, cleanse the filtered data (The non-customer names) and then union the data back together.
  3. You could also add a RecordID to the Date field before using Cross Tab and using the RecordID to ensure that column names are always the same.

Let me know if any of these work, or if I'm still misunderstanding something, so that I can better assist!

binuacs
21 - Polaris

@maygross Multi-Field formula tool has the option to select fields based on the data type. If your date fields are in DateTime data type then select that option, One thing you cannot assign a blank value to the DateTime field data type, it will throw a conversion error and assign NULL values. One option is to change the data type of your date fields into String and then assign the blank value if any of the values is NULL

 

image.png

apathetichell
19 - Altair

likewise - datacleanse has the ability to select only certain fields to "cleanse."

flying008
15 - Aurora

Hi, @maygross 

 

Maybe you can upload sample data of input and want output to explain your need.

Labels
Top Solution Authors