Compare string sort order

1

I have a very specific type of comparison to make. My data looks like this:

Attribute FilterFrom FilterTo
TCD FK01 FK03
Attribute ValueFrom ValueTo
TCD FK01 null
TCD FK02 FK04
TCD FK05 null

The top table is a filter set for identifying relevant rows in the data. The bottom table is the data (SAP Authorizations).

I need to use the row in the top table to identify the first two rows in the second table (because the ValueFrom-ValueTo range overlaps with the FilterFrom-FilterTo range.

So far, I've tried using a "Filter by condition" step with the following logic (using efficient range intersection):

[FilterFrom]<[ValueTo] and [ValueFrom]<[FilterTo]

but that doesn't work because "less than" and "greater than" are not defined for strings. Any ideas how I might go about solving this?

The only string comparison function I could find is setsort, and there's a roundabout way of getting that to work:

    (
        setsort([FilterFrom]&","&[ValueTo]) = [FilterFrom]&","&[ValueTo]
    and
        setsort([ValueFrom]&","&[FilterTo]) = [ValueFrom]&","&[FilterTo]
    )

but that'd probably qualify as a hack. Are there other ways?

I'm asking because the above example is simplified. In the real data, each of these columns allows for a wildcard at the end (e.g. "FK0*"). It would be good to have a few option, to see which one I can extend most easily to the wildcard scenario.

The above code would become this monstrosity:

// match with *
// - removing the * from [FilterFrom] or [ValueFrom] is effective because
//   > "abc" < "abc*"
//   > no string s exists such that "abc" < s < "abc*"
// - replacing the * in [FilterTo] or [ValueTo] would be effective if a replacement ¤ can be found such that
//   > "abc*" <= "abc¤" for any value of *
//     on ANSI code page 1252, that character is ÿ (with ASCII code 255)
    (
        setsort(keepbefore([FilterFrom],"*")&","&replace([ValueTo],"*","ÿ")) = keepbefore([FilterFrom],"*")&","&replace([ValueTo],"*","ÿ")
    and
        setsort(keepbefore([ValueFrom],"*")&","&replace([FilterTo],"*","ÿ")) = keepbefore([ValueFrom],"*")&","&replace([FilterTo],"*","ÿ")
    )
2

Can you clarify what values your data might contain? Are the values going to always be the same pattern?

The examples you give are all “FK##”. I’m guessing you can’t just disregard the “FK” at the start and still need to use this in the comparison with the filter? Assuming it does need to be included, I’m wondering if you can simply split the values into the initial 2 alphabetic characters and then the following number. You can then compare the text characters match the filter and then whether the numbers are greater than or less than.

Regards
Matt