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.)
Site | Latitude | Longitude | 1900 | Expected result |
03RW201-1 | 38.70041666 | -93.27055555 | 12.24 | 03RW201-1 |
03RW201-2 | 38.70041666 | -93.27055555 | 22.31 | 03RW201-2 |
03RW201-3 | 38.70041666 | -93.27055555 | 25.11 | 03RW201-3 |
03RW203-1 | 38.74625 | -93.22355555 | 8.37 | 03RW203-1 |
03RW203-2 | 38.74625 | -93.22355555 | 6.23 | 03RW203-2 |
03RW300-1 | 39.751 | -94.8405 | 92.22 | 03RW316-1 |
03RW300-2 | 39.751 | -94.8405 | 71.56 | 03RW300-2 |
03RW300-3 | 39.751 | -94.8405 | 64.02 | 03RW300-3 |
03RW301-1 | 39.7794 | -94.7931 | 41.01 | 03RW301-1 |
03RW301-2 | 39.7794 | -94.7931 | 5.32 | 03RW301-2 |
03RW301-3 | 39.7794 | -94.7931 | 28.08 | 03RW301-3 |
03RW302-1 | 39.85939444 | -94.81692777 | 15.54 | 03RW302-1 |
03RW302-2 | 39.85939444 | -94.81692777 | 5.64 | 03RW302-2 |
03RW302-3 | 39.85939444 | -94.81692777 | 14.97 | 03RW302-3 |
03RW312-1 | 40.37565555 | -94.85741666 | 4.06 | 03RW312-1 |
03RW312-2 | 40.37565555 | -94.85741666 | 2.83 | 03RW312-2 |
03RW312-3 | 40.37565555 | -94.85741666 | 4.17 | 03RW312-3 |
03RW313-1 | 39.73430555 | -94.79005 | 25.04 | 03RW313-1 |
03RW313-2 | 39.73430555 | -94.79005 | 45.29 | 03RW313-2 |
03RW313-3 | 39.73430555 | -94.79005 | 34.94 | 03RW313-3 |
03RW316-1 | 39.75501666 | -94.28194166 | 8.66 | 03RW316-1 |
03RW316-2 | 39.75501666 | -94.28194166 | 34.92 | 03RW316-2 |
03RW316-3 | 39.75501666 | -94.28194166 | 6.29 | 03RW316-3 |
03RW317-1 | 39.70467777 | -94.88660555 | 27.72 | 03RW317-1 |
03RW317-2 | 39.70467777 | -94.88660555 | 28.71 | 03RW317-2 |
03RW317-3 | 39.70467777 | -94.88660555 | 29.85 | 03RW317-3 |
03RW318-1 | 39.55928888 | -95.03296944 | 19.32 | 03RW318-1 |
03RW318-2 | 39.55928888 | -95.03296944 | 6.46 | 03RW318-2 |
03RW318-3 | 39.55928888 | -95.03296944 | 6.31 | 03RW318-3 |
Please Advice.
Solved! Go to Solution.
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?
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.
no problem, glad i could help!
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.
Site | Latitude | Longitude | 1900 |
03RW201-1 | 38.70041666 | -93.27055555 | 12.24 |
03RW201-2 | 38.70041666 | -93.27055555 | 22.31 |
03RW201-3 | 38.70041666 | -93.27055555 | 25.11 |
03RW203-1 | 38.74625 | -93.22355555 | 8.37 |
03RW203-2 | 38.74625 | -93.22355555 | 6.23 |
03RW300-1 | 39.751 | -94.8405 | 92.22 |
03RW300-2 | 39.751 | -94.8405 | 71.56 |
03RW300-3 | 39.751 | -94.8405 | 64.02 |
03RW301-1 | 39.7794 | -94.7931 | 41.01 |
03RW301-2 | 39.7794 | -94.7931 | 5.32 |
03RW301-3 | 39.7794 | -94.7931 | 28.08 |
03RW302-1 | 39.85939444 | -94.81692777 | 15.54 |
03RW302-2 | 39.85939444 | -94.81692777 | 5.64 |
03RW302-3 | 39.85939444 | -94.81692777 | 14.97 |
03RW312-1 | 40.37565555 | -94.85741666 | 4.06 |
03RW312-2 | 40.37565555 | -94.85741666 | 2.83 |
03RW312-3 | 40.37565555 | -94.85741666 | 4.17 |
03RW313-1 | 39.73430555 | -94.79005 | 25.04 |
03RW313-2 | 39.73430555 | -94.79005 | 45.29 |
03RW313-3 | 39.73430555 | -94.79005 | 34.94 |
03RW316-1 | 39.75501666 | -94.28194166 | 8.66 |
03RW316-2 | 39.75501666 | -94.28194166 | 34.92 |
03RW316-3 | 39.75501666 | -94.28194166 | 6.29 |
03RW317-1 | 39.70467777 | -94.88660555 | 27.72 |
03RW317-2 | 39.70467777 | -94.88660555 | 28.71 |
03RW317-3 | 39.70467777 | -94.88660555 | 29.85 |
03RW318-1 | 39.55928888 | -95.03296944 | 19.32 |
and output should look like this:
Cas-Sec | Latitude | Longitude | 1900 | EXPECTED RESULT |
03RW201-1 | 38.70041666 | -93.27055555 | 12.24 | 03RW201-1 |
03RW201-2 | 38.70041666 | -93.27055555 | 22.31 | 03RW201-2 |
03RW201-3 | 38.70041666 | -93.27055555 | 25.11 | 03RW201-3 |
03RW203-1 | 38.74625 | -93.22355555 | 8.37 | 03RW203-1 |
03RW203-2 | 38.74625 | -93.22355555 | 6.23 | 03RW203-2 |
03RW300-1 | 39.751 | -94.8405 | 92.22 | 03RW316-1 |
03RW300-2 | 39.751 | -94.8405 | 71.56 | 03RW300-2 |
03RW300-3 | 39.751 | -94.8405 | 64.02 | 03RW300-3 |
03RW301-1 | 39.7794 | -94.7931 | 41.01 | 03RW301-1 |
03RW301-2 | 39.7794 | -94.7931 | 5.32 | 03RW301-2 |
03RW301-3 | 39.7794 | -94.7931 | 28.08 | 03RW301-3 |
03RW302-1 | 39.85939444 | -94.81692777 | 15.54 | 03RW302-1 |
03RW302-2 | 39.85939444 | -94.81692777 | 5.64 | 03RW302-2 |
03RW302-3 | 39.85939444 | -94.81692777 | 14.97 | 03RW302-3 |
03RW312-1 | 40.37565555 | -94.85741666 | 4.06 | 03RW312-1 |
03RW312-2 | 40.37565555 | -94.85741666 | 2.83 | 03RW312-2 |
03RW312-3 | 40.37565555 | -94.85741666 | 4.17 | 03RW312-3 |
03RW313-1 | 39.73430555 | -94.79005 | 25.04 | 03RW313-1 |
03RW313-2 | 39.73430555 | -94.79005 | 45.29 | 03RW313-2 |
03RW313-3 | 39.73430555 | -94.79005 | 34.94 | 03RW313-3 |
03RW316-1 | 39.75501666 | -94.28194166 | 8.66 | 03RW316-1 |
03RW316-2 | 39.75501666 | -94.28194166 | 34.92 | 03RW316-2 |
03RW316-3 | 39.75501666 | -94.28194166 | 6.29 | 03RW316-3 |
03RW317-1 | 39.70467777 | -94.88660555 | 27.72 | 03RW317-1 |
03RW317-2 | 39.70467777 | -94.88660555 | 28.71 | 03RW317-2 |
03RW317-3 | 39.70467777 | -94.88660555 | 29.85 | 03RW317-3 |
03RW318-1 | 39.55928888 | -95.03296944 | 19.32 | 03RW318-1 |
Thanks
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.
oops and deselect the DNU_expected result for this workflow - it should drop when you connect your live data anyway though...
Yes I did that already..thank you very much Jarrod..you are awesome.