Extract Position using Match

[aman_goyal]

Hi Community,

I have 2 columns in file and I want to match them if value in column 1 exists in column 2 then we need position of column where value exists. If value not exists then position of nearest smaller value for the value should be returned.

It is same as MATCH(lookupvalue, lookuparray,1) in excel that I need to implement in Alteryx.

PFA attached file, where column B  value need to look in column A. The output column is the result. in this file few records are there, but need to implement for much data. 

Needed Community help !!

[afv2688]

Hello @aman_goyal ,

Let me know if this works for you 🙂

Regards

[aman_goyal]

Hello @afv2688 ,

I think I provided wrong data, the output column in this file is the final output that I want to achieve. Can you please provide me workflow for attached input.

If possible, please explain little bit.

[afv2688]

Hello  @aman_goyal ,

Please let me know first if the output result is correct and if so I will afterwards explain it. (Explaining something that is wrong wouldn't make any sense :P)

Regards

[aman_goyal]

Hi @afv2688,

Thank You !!

Yes , the output is correct. I have few questions:
In the macro input, did you add manually data? Can we configure our input file. Please explain the input part of macro. 
In the macro, the filter where diff >0. If the diff <0 then, what needs to do?

It will be very helpful if you can explain this whole workflow.

Regards,

Aman

[afv2688]

Hello @aman_goyal ,

I did not add any manual data. everything is totally dynamic.

I do not know what you mean by the diff being <0 you want to have it possible to be changed rapidly or you want to also include all those below 0?

What the workflow does in a dynamic way is it takes all the values from the list and analyzes them one by one (all the bt) and compares them by calculating the difference with the pol numbers.  

With the join what i do is eliminating the values that are on the same line as the actual bt that I am taking

Then with the summarize tool I take the record id corresponding to the lowest difference and that is the value I return.

You will see that with the control parameters I am updating manually the values therefore they are not there hardcoded, just used as dummies for the macro creation.

Regards

[aman_goyal]

Hello @afv2688 

In the macro input, we have copied the values and paste. Is there any way that we take input from other workflow like dynamic input.

Scenario:
Suppose there is one workflow where we are taking some source data and after processing implementing ID_NUM fields using formula and for that ID_NUM fields we need to extract position for which we are using macro. The Macro input should be from the workflow. 
OR

Is there any way without macro we can implement this position extract?

Also, the position that it returns should be like- ID_NUM_POL largest value that is less than or equal to ID_NUM_BT.
Example- For BT value 10302021.09, the same not exists in ID_POL, it takes less than value from BT_VALUE '10302021.09' that is 10302020.09 that row 3 so return position 3.

ID_NUM_POL	ID_NUM_BT
10302020.09	10302021.09
10302020.09	10302021.09
10302020.09	10302021.23
10312020.09	10312021.09
10312020.09	10312021.09
10312020.09	10312021.23
10322020.09	10322021.09
10322020.09	10322021.09
10322020.09	10322021.23

ID_NUM_POL	ID_NUM_BT	Position
10302020.09	10302021.09	3
10302020.09	10302021.09	3
10302020.09	10302021.23	3
10312020.09	10312021.09	6
10312020.09	10312021.09	6
10312020.09	10312021.23	6
10322020.09	10322021.09	9
10322020.09	10322021.09	9
10322020.09	10322021.23	9

Thanks & Regards,
Aman