Filter values falling in range

I want to extract values that are falling in below range

13700-139ZZ
J0000-JZZZZ
HB000-HBZZZ
HC000-HC000
G0000-GZZZZ

where Z indicates value from 0 to 9 and A to Z

so for example 13701,1370A,137BB,139A6 would all be considered in range. Can anyone help me with formula to filter these values from dataset? Thank you

sample.xlsx

Workflow

Datasets

Regex

Transformation

Fuzzy Match

Data Investigation

Accepted answers

All comments

binu_acs

@srk0609 one way of doing this

Filter_14052024.yxmd

srk0609

This won't work cos it pulls Id starting with 136 example it will pull record 13600, 13699 but I want only values in 13700 to 139ZZ to get selected not starting 136 or 135. Can you please update formula

srk0609

I modified it to get solution. Thank you for your help.

AGilbert

@srk0609what was your solution?

You can convert the 5-character string on a base-36 numeric system. Similar to a hexadecimal base-16 system. I'm testing on my end; so, I'd appreciate any feedback you have.

To implement, you would split the range string on the '-' character and convert both numbers with the macro as an upper and lower limit. Then convert each of your target values to have comparable values for a >= AND <= filter expression.

Edit: I guess this isn't really a hash function, so I removed that language.

hash map.png

main.yxzp

srk0609

what changes should I make to the workflow to exclude 13700 - 13707. The field is a V_WString format

srk0609

what are the changes I should make to the workflow to exclude 13700 - 13707. The field is a V_WString format

Qiu

@srk0609
Just go ahead and filter😁 Suprise! Alteryx has taken this into consideration.

0521-srk0609.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703