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

Compare two columns and return the Lower one with 3rd column output

smfhsan2005
8 - Asteroid

Hello Experts, I am new to Alteryx,  I have a tasks of comparing two columns  and return with the 3rd column vaue which has the lowest of the value..any help will be highly appreciated.

 

eg..If Column B2 >90% in this case(96.45) is greater than A2(84.26) then jump to value of column B which has less than90% in this case B3 and return value from Colum A ML60161 and if Column B already less than 90% then return Same name as Column Name.

 

 

NameABEnd result
ML0310984.2696.45ML60161
ML6016176.4188.42ML60161
ML0311080.2493.42ML03121
ML5475566.2996.33ML03152
ML1710356.3395.02ML03121
ML0312129.8183.58ML03121
ML0315245.5273.03ML03152
ML1310741.3981.55ML13107
ML0311140.0284.83ML03111
28 REPLIES 28
geraldo
13 - Pulsar

Hi,

 

 

I don't know if you have already closed your question, but I would like to know why it was unclear what rule you set to fetch <1 mile. Values ​​below 1 mile are only found in comparisons of other lines.


If it is open you could describe a little more of this rule

benakesh
12 - Quasar

Hi @smfhsan2005 ,

Can you clarify    "< 1 miles from B"   ?  B  is  a number  .  Distances  can be  calculated  between  2 points . 

 

 

Algorithm 

1st case : if B value is >90% and also >A then D should have the Name which is < 1 miles from B. in continuation to this scenario we have

2nd case: if C value is >90% and also >B then E should have the Name which is < 1 miles from C but in this case C and B are mostly >90%

 

NameLatLongABCDE
ML0310943.090194-87.96597284.2696.4598.79ML60161 

Is  ML60161   expected  result ?   It is  not  < 1 mile from  ML03109.  

1st case  and  2nd Case :    Are these  columns D and  E  i.e  outputs  ?   

 

The attached  wf  calculates nearest point  for all  points  and they all are  > 1 mile .  

You  can  revise  algorithm  to make it more easy  for  community  to  provide  expected  results .   

clipboard_image_0.png

 

geraldo
13 - Pulsar

hi,

 

 


I worked out a workflow maybe it can be a little complicated. Includes 4 rule-based possibilities:


eg..If Column B2 >90% in this case(96.45) is greater than A2(84.26) then jump to value of column B which has less than90% in this case B3 and return value from Colum A ML60161 and if Column B already less than 90% then return Same name as Column Name.

1st case : if B value is >90% and also >A then D should have the Name which is < 1 miles from B. in continuation to this scenario we have

2nd case: if C value is >90% and also >B then E should have the Name which is < 1 miles from C but in this case C and B are mostly >90% but I need the formula for it in future use.

Note: It will be ok if the names been repeated within 1 mile.

smfhsan2005
8 - Asteroid

Yes D and E are outputs.

 

 

 

 

Cascade

LatitudeLongitudeABCB>AC>B
ML0310943.090194-87.96597284.2696.4598.79Jump to ML03111 or cascade nearest to ML03109 but less than 90%Here just the formula is ok since there are no values less than 90%
smfhsan2005
8 - Asteroid

This helped me a lot..thanks benakesh.

smfhsan2005
8 - Asteroid

Hello All,

 Thanks for your help in my previous task, here I have another task with additional requirement, I am stuck at the formula where I need to find the column with greater than 80% value(Which I have already done it) and jump to the value less than 60% in the same column within 3 miles of radius.clipboard_image_0.png

Column H is the output where you see if Column High Y to Low Y and there is no jump since the value is less than 80% but when you see the Row7 has 92.22 value jumped from 03RW300-1 to 03RW300-2 and this value is on the same location (lat long are same), here I need the out should be little away from that exact location which has different row value which is less than 60% within 3 miles of radius.

 

just an example eg..RW300-1 to 03RW312-1 which is 4.06 and within 3 miles of 03RW3001-1

 

Please help.

smfhsan2005
8 - Asteroid

Hello All,

 Sorry forgot to attache the tool and input..

Thanks for your help in my previous task, here I have another task with additional requirement, I am stuck at the formula where I need to find the column with greater than 80% value(Which I have already done it) and jump to the value less than 60% in the same column within 3 miles of radius.

 

Column H is the output where you see if Column High Y to Low Y and there is no jump since the value is less than 80% but when you see the Row7 has 92.22 value jumped from 03RW300-1 to 03RW300-2 and this value is on the same location (lat long are same), here I need the out should be little away from that exact location which has different row value which is less than 60% within 3 miles of radius.

 

just an example eg..RW300-1 to 03RW312-1 which is 4.06 and within 3 miles of 03RW3001-1

 

Please help.

benakesh
12 - Quasar

Hi @smfhsan2005 ,

Some input files are not shared .  Can you attach all input files ?

I am stuck at the formula  :   There are  3 formula tools  in wf  .  Which formula is not  working  ? 

 

Also  sample  input  data and expected  output data  can help .

 

smfhsan2005
8 - Asteroid

Hi @benakesh please find attached input.  

smfhsan2005
8 - Asteroid

@benakesh Need to update in all formulas..

 

Input and output I attached to my previous message, please let me know if you could get it.

Labels