This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a dataset with lat/lon information. My problem is that I have multiple duplicate entries and would like to interpolate between them.
For instance say I have 1000 unique GPS points, but 10,000 recorded data points (associated with other unique information that updated faster than GPS when collecting).
It's not always a fixed amount of points between unique GPS points so it has to be somewhat of a dynamic solution. I can probably write a C# or python script to handle it and run it externally from the run command, but that's a pretty ugly solution, is there a way to handle it within alteryx?
I was thinking multi-line formula, but the fact that there are different numbers of duplicates in a row is throwing me off.
Assuming the answer to @Philip question is yes, i.e. straight-line interpolation, then I believe the following will work. Although there is very likely a much faster/cleaner way to get there (this felt like a lot of tools to get to a fairly simple result)... it should work.
1. Add RecordID
2. Find Unique Lat/Lon combos
3. Use Multi-Row Formulas to find the difference between each Lat/Lon Combo, then divide by the number of rows between them, i.e. if there is a difference of 0.2 between RecordID 1 and RecordID 3, then the incremental amount that will be added to RecordID 2 when the data is brought back together should be 0.1, or 0.2/(3-1).
4. Find & Replace tool to join the NewLat & NewLon incremental amounts back to the original data by RecordID
5. Sort by Descending RecordID
6. If NewLat/NewLon is null, then use the row above
7. Sort by Ascending RecordID
8. Tile by Unique Value to find groupings by Lat/Lon combo
9. Formula: If the Tile Sequence = 1 then leave Lat/Lon as is, otherwise add the NewLat/NewLon to determine the interpolated location
Let me know if this gets you the data you need, or if additional tweaking/simplification is needed?
I was having trouble working out the correct way to use the multi-line formula, but I think how you added the recordID and separated out unique values to put it back together later is the right way to approach it.
What if there are different numbers of rows. For example, what if you have two null value in location, three in another, and 1 in another? In excel, you'd hit F4 to set the second value and row ID but I'm not sure how you would do that in Alteryx.
Are you asking how would you interpolate the different number of redundant rows between each unique location? So each individual interpolation could be different lengths? I believe the solution above handles that.