Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Find distance between 2 column and get result which has lesser value from the third Column

Highlighted
Asteroid

Hello Experts, I need some help in getting the distance

1. between below mentioned Lat and Long,

2. then find the value which has greater than 70% and return output  which is within 3 miles and have value less than 60% with respect to the first column.

eg., check the value in Red(92.2), and it will jump to the value in Green(8.66) and get the result as 03RW316-1 since the value is below 60% and within 3 miles of radius.( just an example it moght not be exact within 3 miles.)

 

SiteLatitudeLongitude1900Expected result
03RW201-138.70041666-93.2705555512.2403RW201-1
03RW201-238.70041666-93.2705555522.3103RW201-2
03RW201-338.70041666-93.2705555525.1103RW201-3
03RW203-138.74625-93.223555558.3703RW203-1
03RW203-238.74625-93.223555556.2303RW203-2
03RW300-139.751-94.840592.2203RW316-1
03RW300-239.751-94.840571.5603RW300-2
03RW300-339.751-94.840564.0203RW300-3
03RW301-139.7794-94.793141.0103RW301-1
03RW301-239.7794-94.79315.3203RW301-2
03RW301-339.7794-94.793128.0803RW301-3
03RW302-139.85939444-94.8169277715.5403RW302-1
03RW302-239.85939444-94.816927775.6403RW302-2
03RW302-339.85939444-94.8169277714.9703RW302-3
03RW312-140.37565555-94.857416664.0603RW312-1
03RW312-240.37565555-94.857416662.8303RW312-2
03RW312-340.37565555-94.857416664.1703RW312-3
03RW313-139.73430555-94.7900525.0403RW313-1
03RW313-239.73430555-94.7900545.2903RW313-2
03RW313-339.73430555-94.7900534.9403RW313-3
03RW316-139.75501666-94.281941668.6603RW316-1
03RW316-239.75501666-94.2819416634.9203RW316-2
03RW316-339.75501666-94.281941666.2903RW316-3
03RW317-139.70467777-94.8866055527.7203RW317-1
03RW317-239.70467777-94.8866055528.7103RW317-2
03RW317-339.70467777-94.8866055529.8503RW317-3
03RW318-139.55928888-95.0329694419.3203RW318-1
03RW318-239.55928888-95.032969446.4603RW318-2
03RW318-339.55928888-95.032969446.3103RW318-3

Please Advice.

Highlighted
Bolide
Bolide

I don't understand what you mean by "value less than 60%" - less than what? The 1900 column is confusing as well - what is that value? I see you have the expected result in Red is the only one that differs from the Site field. Can you provide some more insight on these figures?

 

Thanks!

Highlighted
Asteroid

Hi Jarrod,

    1900 column is in percentage: if you see first value of 1900 column is 12.24(which is less than 70%) so the result should be exactly like Row 2 which is 03RW201-1 .then if we move down continuously like Row 1,  then coming to Row 7: Column 1900 is 92.22(greater than 70%)so the example  Expected result is the site name other than the Row 7 Site name which is 03RW316-1 instead of 03RW300-1 since the value of this Row is greater than 70% and jump to Row 22 which has 1900 column value 8.66 (less than 60%)...the expected result should have the value less than 60% within 3 miles of site column.

 

Please let me know it it helps.

 

Basically Expected result should have multiple conditions, one is replace site and value>70 with lesser than  60% within 3 miles

Highlighted
Asteroid

@jarrod adding to that condition, if it not less than 3 miles let do within 10 miles also..miles are dynamic.

Highlighted
Fireball

Yes it would be helpful to have some more information as the distance between the two points you mention is about 30 miles, so I don't follow the specific need.

 

N.B. I was able to get the distance between the points by creating a centriod using the longitute and latitude columns provided and then joined Expected Site with Site which gives two centroids for one record which enables you to use the distance tool.

 

Spatial Solution.PNG

Bolide
Bolide

Gotcha, see the attached workflow for the flow as i understand it along with some notes.

Highlighted
Bolide
Bolide

just saw your notes on miles being dynamic - that can be accomplished by bringing in a field and just referencing the field instead of the hard-coded mileage. adding a secondary limit is the same thing, just add another process to look for a larger distance, but what you'd probably want to do is just look for the largest mileage acceptable then sort by Distance before the unique to find the closest site. the workflow may need some tweaking for your specific purpose.

Highlighted
Asteroid

Thanks Jarrod, wonderful approach, mine was little long approach, really appreciate it..

 

can we tweak a little bit in the formula and try to avoid the result from the  at the same location..like here Site column Lat and Long are same as the site in the expected result column, instead can we do any other lat and long? as in yellow.

 

clipboard_image_0.png

 

Highlighted
Bolide
Bolide

i just modified the workflow to add in a text to columns (to split the site name) and the filter now has a "&& site1 != source_site1" condition.

Highlighted
Asteroid

In this approach, all the values greater than 70% from 1900 columns is gone:) and there is no replaced value in expected column.

Labels