community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Clearing Null entries within columns

Highlighted
Meteor

Hello everyone,

 

I have a large dataset that covers several sales territories. I've been trying to Crosstab the data to organize account numbers by territory, but that approach generates a large amount of nulls since the accounts do not duplicate across territories. My data looks like this prior to cross tab:

 

missing1234
missing2345

Arizona

 

3456
Arkansas4567
Colorado5678
Colorado6789

 

It appears like so After the cross tab:

 

missingArizonaArkansasColorado
1234[null][null][null]
2345[null][null][null]
[null]3456[null][null]
[null][null]4567[null]
[null][null][null]5678
[null][null][null]6789

 

I am trying to get it to output like this:

 

missingArizonaArkansasColorado
1234345645675678
2345[null][null]6789
[null][null][null][null]
[null][null][null][null]

 

Up until this point, I've used the delete cells menu in Excel to wrangle these into place, but I am migrating this report to a non-excel format so that will be a cumbersome extra step.

 

I'll have around 40-50 column headers. My initial impulse is to use a multi-row formula, but with so many headers that seems like an inefficient solution. Is there a more elegant way that I've overlooked, or will I just need to use lots of multi-row tools?

Try using the 'SORT' tool. Prior to that I would include all the locations you might receive data for with a 'TEXT INPUT' connected to a 'UNION' tool that would be paired up with your original data. You could sort by column and if you provide all the columns that could exist then you won't receive an error/unsorted exception.

Bolide

Hi alexg1,

 

What about something like this:

 

- concatenate values for every column (applying string format in the prior step)

 

- transpose and calculate how many values there are in each concatenation (max value across all records is appended and used as a parameter for Text To Columns tool in the macro to align format)

 

- cross-tab after the macro gives the below output - this is flexible for all your potential columns

 

img.JPG

 

Thanks,

Rafal

 

#Excuse me, do you speak Alteryx?

Alteryx Certified Partner

Hi @alexg1 

 

You need to create field that would define what row each value should be attributed to. This value would be configured in your Crosstab tool as one of the 'Grouping' fields.

image.png

 

In this example, I'm using a Multi-Row formula tool to create "row_num" which is simply incrementing the previous rows "row_num" value +1 and resetting (Group By) on every Field1 value, which I assume is the territory. Throw a Select tool after the crosstab to remove that "row_num" field if needed.

image.png

Hope this helps!


Jimmy
Teknion Data Solutions

Labels