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

[benakesh]

Hi @smfhsan2005 ,

In wf these are the  input files  and they are not shared .

Kansas cas-sec kpis.csv

Site_Topology_Report_oa217993_12_03_2018_09_52_04.csv

This file  is shared  but  not used in wf.  

 

Kansas cas-secjump4withlatlong.csv

 

[smfhsan2005]

Sorry for confusion please see attached.

[benakesh]

Hi @smfhsan2005 ,

The [Cas-Sec]  is not in  input file :  Kansas caskpi.csv  . The error is unknown variable .

iif([RF Utilization(Max)800] <= 70 , [Cas-Sec],Null())   

[smfhsan2005]

Hi @benakesh please see this file as input. Sorry the earlier one was missing cas-sec column.

Thanks

[benakesh]

Hi @smfhsan2005 ,

These are the changes  in  wf .

Removed duplicates in  sitetopologykansas.csv .

Cross tab before output to keep  1st , 2nd and 3rd  closest locations in same row .

You can remove  grand total row in  Kansas cas-sec kpis.csv if it is not required. 

Hope this helps . 

[smfhsan2005]

Hello @benakesh

@benakesh, thanks for the help..but this is giving the same output as earlier..

when you see the the blue circle its jumping from KC03RW300-1 to KC03RW300-3(for us this is considered the same location since lat and long are same for this location, we need to avoid this and select the one apart from the same location and that location shoud be within 3 miles. Please see attached..please pardon my drawing ..example..here if KCRW302-3. is within 3 miles of KCRW300-1 should jump to KCRW302-3.

[benakesh]

Hi @smfhsan2005 ,

You can change jump/nojump formula to check 9 characters  ( left ) .

[smfhsan2005]

Hi @benakesh ..thats great It worked in most of the cases but ..some of the results still looks the same since.."no jump" is only the string we are entering in formula  but in practical can it jump to the other cascade like the one in blue circle or can we restrict the jump value (in this case RF utilization 1900) to less than 60%(the value here is 64.22 in blue).

I really appreciate your time many thanks.

[smfhsan2005]

Hi @benakesh  is that possible to use the Multi formula or any other formula where we can consider only 20 rows and find the minimum of that instead of distance within 3 miles for now then later we will consider distance.. and replace the Null value coming out of the first formula...I tried to use Multi row formula with Row-10 it is considering 10th row..I need to see "within" 10 rows. 

Thanks