Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

smfhsan2005
8 - 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.

16 REPLIES 16
jarrod
ACE Emeritus
ACE Emeritus

are you saying that you want the values replaced with the 'expected' 1900 values? or are you saying that the workflow i upload isn't working as expected? 

smfhsan2005
8 - Asteroid

Hi Jarrod,

            Sorry for confusion I got the result, I did change your formula as you mentioned in the comment in WF, from [RecordID] < [Source_RecordID] to [RecordID] != [Source_RecordID] and I got the accurate results. You saved my day...really appreciate it.

 

Many thanks.

 

 

jarrod
ACE Emeritus
ACE Emeritus

no problem, glad i could help!

smfhsan2005
8 - Asteroid

Hi @jarrod..Sorry I am back again..when I tried to use the WF for my bulk data the output is completely different from the morning one. seems like the WF is taking expected result from

the input..I tried to add a column using formula tool but the result I am getting is below 

Please advice

 

FYI: The actual input looks like this. 

SiteLatitudeLongitude1900
03RW201-138.70041666-93.2705555512.24
03RW201-238.70041666-93.2705555522.31
03RW201-338.70041666-93.2705555525.11
03RW203-138.74625-93.223555558.37
03RW203-238.74625-93.223555556.23
03RW300-139.751-94.840592.22
03RW300-239.751-94.840571.56
03RW300-339.751-94.840564.02
03RW301-139.7794-94.793141.01
03RW301-239.7794-94.79315.32
03RW301-339.7794-94.793128.08
03RW302-139.85939444-94.8169277715.54
03RW302-239.85939444-94.816927775.64
03RW302-339.85939444-94.8169277714.97
03RW312-140.37565555-94.857416664.06
03RW312-240.37565555-94.857416662.83
03RW312-340.37565555-94.857416664.17
03RW313-139.73430555-94.7900525.04
03RW313-239.73430555-94.7900545.29
03RW313-339.73430555-94.7900534.94
03RW316-139.75501666-94.281941668.66
03RW316-239.75501666-94.2819416634.92
03RW316-339.75501666-94.281941666.29
03RW317-139.70467777-94.8866055527.72
03RW317-239.70467777-94.8866055528.71
03RW317-339.70467777-94.8866055529.85
03RW318-139.55928888-95.0329694419.32

 

and output should look like this:

Cas-SecLatitudeLongitude1900EXPECTED 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

clipboard_image_0.png

Thanks

jarrod
ACE Emeritus
ACE Emeritus

no problem, See attached. 

I had to add a formula tool to make an expected result field for the records that were within the acceptable threshold. Then for the records that need a different site, we have to rename the "source_site" to "Expected result". Note, the original was taking the expected result from the source_expected result when it should have been Source_site anyway. so this actually fixed 2 issues. 

jarrod
ACE Emeritus
ACE Emeritus

oops and deselect the DNU_expected result for this workflow - it should drop when you connect your live data anyway though...

smfhsan2005
8 - Asteroid

Yes I did that already..thank you very much Jarrod..you are awesome.

Labels