Join on Discrete and Continuous Data

[Akash2093]

Hi

I have a situation in my current project to join two data source where the key column contains discrete data values and continuous/logical ranges on the right side of join. Example below -

Source/Discrete Data value -

1

5

10

15

25

Continuous values -

Case - I (continuous ranges)

1-5 (Should match with 1 & 5 on Source data)

6-10 (Should match with 10 on Source data)

11-15 (Should match with 15 on Source data)

16-20 (No Matches)

21-25 (Should match with 25 on Source data)

Case -II (logical ranges)

<> 5 (Anything other than 5 should match from source data)

10 or 15 (only 10 or 15 should match)

The workaround, i have done is splitting the range into two columns, doing a cross join on both data source and using filter tool to compare source value with ranges.

But this is not very efficient way as apparent, just seeking opinion can there be better ways to do it? If anyone else faced similar issue earlier, please share the approach followed.

[clmc9601]

Hi @Akash2093,

I generated logical expressions based on the cases you mentioned and then used a batch macro to evaluate which data records made those expressions true. When the expressions are true, the macro only appends the range number to the expression, but you can join it back to the original case data to find any additional information. It only outputs the equivalent of an inner join (I say equivalent because it's evaluating and filtering true/false rather than doing an actual join).

Note that I adapted your logical cases to be in Alteryx-friendly syntax ("<>5" became "!=5" and "10 or 15" became "IN(10,15)") so any other logical cases added will have to be written in Alteryx syntax as well. 

I'm pretty sure I understood your description, but please let me know if I missed something. I hope this helps!

Batch macroWorkflow

[clmc9601]

Hi @Akash2093, just wanted to follow up-- did this solution work for you? Thanks. 

[Akash2093]

Hi

thanks for your response however client is skeptical about using Alteryx Macros.

Also, to adapt the logical ranges in Alteryx syntax, i.e. <>80 to !=80, will again be manual intervention.

As a workaround, i am adding a Match flag column which indicates if the values in question is to be matched or to be excluded.

And then i do append between source & target data after which i specify my condition using a filter tool that has logical expression based on the Match flag column.