Hi there, This afternoon's Alteryx is sponsored by Tylenol. I've got some data with holes in it - not sure why - but I have figured out there is a pattern to the holes. I have unique account numbers with a product code, address, and lat/long. Each row of my data represents an account number and a unique product code. If an account number has more than one product code assigned, say three, then it will actually take up 3 rows in my dataset. And that's fine - it's how the client wants the data. However, if the account number has more than one product code assigned, for some reason the data does duplicate the address (good) but does not duplicate the lat/long (bad). It's just a problem with the provided data, not a matching thing. It's holey data. So, I might have data that looks like this: Account #Product CodeAddressLatLong0011234101 1st Streetxx.xxxxxyy.yyyyy0021123102 2nd Streetxx.xxxxxyy.yyyyy0021223102 2nd StreetNULLNULL0021113102 2nd StreetNULLNULL So, in the example, my accounts with multiple product codes don't show the lat/long. I need to write a formula of some kind that basically says: If account numbers match, and one row has lat/long and the others contain NULL, then copy lat/long from the row that contains that data. I think this is a multi-row formula thing - but I'm having trouble getting my pea-sized brain around it. Am I thinking along the right lines? EDIT: I think the solution is the multi-row deal: It's not this but it's got to be something along these lines? IF [Row-1:Unique_ID]=[Unique_ID] AND [Lat]=NULL() THEN [Lat]=[Row-1:Lat] else [Lat] endif The above sure as heck doesn't work, but I think I'm getting close.

Copying data from another row based on criteria

Hi there,

This afternoon's Alteryx is sponsored by Tylenol.

I've got some data with holes in it - not sure why - but I have figured out there is a pattern to the holes. I have unique account numbers with a product code, address, and lat/long.

Each row of my data represents an account number and a unique product code. If an account number has more than one product code assigned, say three, then it will actually take up 3 rows in my dataset. And that's fine - it's how the client wants the data.

However, if the account number has more than one product code assigned, for some reason the data does duplicate the address (good) but does not duplicate the lat/long (bad). It's just a problem with the provided data, not a matching thing. It's holey data.

So, I might have data that looks like this:

Account #	Product Code	Address	Lat	Long
001	1234	101 1st Street	xx.xxxxx	yy.yyyyy
002	1123	102 2nd Street	xx.xxxxx	yy.yyyyy
002	1223	102 2nd Street	NULL	NULL
002	1113	102 2nd Street	NULL	NULL

So, in the example, my accounts with multiple product codes don't show the lat/long.

I need to write a formula of some kind that basically says:

If account numbers match, and one row has lat/long and the others contain NULL, then copy lat/long from the row that contains that data.

I think this is a multi-row formula thing - but I'm having trouble getting my pea-sized brain around it. Am I thinking along the right lines?

EDIT: I think the solution is the multi-row deal: It's not this but it's got to be something along these lines?

IF [Row-1:Unique_ID]=[Unique_ID] AND [Lat]=NULL() THEN [Lat]=[Row-1:Lat] else [Lat]
endif

The above sure as heck doesn't work, but I think I'm getting close.

Workflow

Datasets

Accepted answers

ravisharma

Hi @EvolveKev

Attaching workflow with multi row formula tool as a solution. Just make sure to select "update existing field" in multi row formula tool.

This could one of the ways to do this. Hope this helps.

Thanks.

Solution on row based data.yxmd

All comments

JoBen

Hi @EvolveKev, if the first account number has a valid lat and long, but every other account number is populated with a null value, why don't you do something like this? This is sampling off the first row grouping off of the account number and then joining back on the account number. You can then deselect the original lat and long and select the lat and long from the right input (from the sample tool)

ravisharma

Hi @EvolveKev

Attaching workflow with multi row formula tool as a solution. Just make sure to select "update existing field" in multi row formula tool.

This could one of the ways to do this. Hope this helps.

Thanks.

Solution on row based data.yxmd

EvolveKev

Hi guys,

I am SO sorry for the late response.

I REALLY appreciate your input.

@JoBen - I couldn't quite get your solution to work, but I love the logic behind it. This is certainly something I will put to use - I never would have thought of using the sample tool.

@ravisharma - This solution is perfect. I made one minor tweak and it worked absolutely flawlessly. Thank you!

Again, thank you both for helping out, and helping so quickly, too! It means a lot to me (and the success of my Alteryxing)!

Kevin

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235