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

smfhsan2005

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.

Name	A	B	End result
ML03109	84.26	96.45	ML60161
ML60161	76.41	88.42	ML60161
ML03110	80.24	93.42	ML03121
ML54755	66.29	96.33	ML03152
ML17103	56.33	95.02	ML03121
ML03121	29.81	83.58	ML03121
ML03152	45.52	73.03	ML03152
ML13107	41.39	81.55	ML13107
ML03111	40.02	84.83	ML03111

Output

Accepted answers

benakesh

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%

Name	Lat	Long	A	B	C	D	E
ML03109	43.090194	-87.965972	84.26	96.45	98.79	ML60161

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 .

2cols_lower1.yxmd

All comments

geraldo

Hi,

This table has a certain number of rows or is it dynamically increasing regularly

[]

benakesh

Hi @smfhsan2005 ,

There is one difference in my result . Can you explain your logic for 4th row ?

2cols_lower.yxmd

smfhsan2005

For now its only few rows, but it will increase dynamically.

smfhsan2005

4th row should show the name from column "name" related to the value which has less than 90% of column B.

benakesh

Row 6 col B value ( 83.58) is < 90 % . Your results shows row 7 value . Is there a reason you skip row 6 and pick

row 7 ?

smfhsan2005

Hello @benakesh. thanks it helped me 90% as per your genuine question yes.the second part of this task is checking the distance between 2 names in Column "name" which is nearest one to each other will become the criteria for 4th column. here we can consider atleast within 2 miles.

Cascade	Latitude	Longitude	A	B	C	Endresult
ML03109	43.090194	-87.965972	84.26	96.45	98.79	ML60161
ML03110	43.07121	-87.95345	80.24	93.42	98.61	ML60161
ML03111	43.11725	-87.93044	40.02	84.83	96.06	ML03121
ML03121	43.07941	-88.0043	29.81	83.58	94.96	ML03152
ML03152	43.08595	-87.925217	45.52	73.03	98.87	ML03121
ML13107	43.09686	-87.98471	41.39	81.55	94.64	ML03121
ML17103	43.115014	-87.964947	56.33	95.02	98.55	ML03152
ML54755	43.074108	-87.976078	66.29	96.33	96.3	ML13107
ML60161	43.098492	-87.94245	76.41	88.42	97.26	ML03111

geraldo

Here's an example that might fit the number of dynamic lines.

attached workflow

[]

CompareByGeraldo.yxmd

smfhsan2005

No there is no particular reason behind it..it should be in sequence which has less than 90%.

I am really sorry for not able to communicate exactly but my complete task is like below...I

Here is the 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% but I need the formula for it in future use.

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

Name	Lat	Long	A	B	C	D	E
ML03109	43.090194	-87.965972	84.26	96.45	98.79	ML60161
ML60161	43.098492	-87.94245	76.41	88.42	97.26	ML60161
ML03110	43.07121	-87.95345	80.24	93.42	98.61	ML03121
ML54755	43.074108	-87.976078	66.29	96.33	96.3	ML03152
ML17103	43.115014	-87.964947	56.33	95.02	98.55	ML03121
ML03121	43.07941	-88.0043	29.81	83.58	94.96	ML03121
ML03152	43.08595	-87.925217	45.52	73.03	98.87	ML03152
ML13107	43.09686	-87.98471	41.39	81.55	94.64	ML13107
ML03111	43.11725	-87.93044	40.02	84.83	96.06	ML03111

smfhsan2005

Thanks Gerarldo. this helps but unable to get it with nearest location.

geraldo

hi,

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

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.

CompareByGeraldo4.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703