Dual() + set analysis: searching for numeric representation via numeric search (vs. expression search)

    Within that example I'd like to explain how to search for the numeric representation of a dual() field with the help of numeric search in set analysis. It is probably slower than using flags... however it's just another idea


    Example data:


    SET vSelectedValue = 5;
    LOAD dual('ABC',5) as FIELD, 1 as VALUE AutoGenerate (1);
    LOAD dual('CDE',3) as FIELD, 2 as VALUE AutoGenerate (1);
    LOAD dual('DEF',5) as FIELD, 3 as VALUE AutoGenerate (1);
    LOAD dual('GHI',6) as FIELD, 4 as VALUE AutoGenerate (1);


    I'd like to find the fields with numeric representation =5.

    An expression like the following doesn't work, because it filters only the text representation:


    Concat({< FIELD={"$(vSelectedValue)"} >} VALUE,' | ')


    I had the idea building "=" and "<>" with the help of "<","<=",">" and ">=".


    So a solution for checking, if the numeric representation equals vSelectedValue could be:


    Concat({< FIELD={">=$(vSelectedValue)<=$(vSelectedValue)"} >} VALUE,' | ')


    And a solution for checking, if the numeric representation not equals vSelectedValue could be:

    Concat({< FIELD={">$(vSelectedValue)<$(vSelectedValue)"} >} VALUE,' | ')


    Calculation speed: numeric search vs. expression search


    numeric search: Concat({< FIELD={">=$(vSelectedValue)<=$(vSelectedValue)"} >} VALUE,' | ')

    expression search: Concat({< FIELD={"=FIELD=$(vSelectedValue)"} >} VALUE,' | ')

    --> 5 million distinct row values:

    LOAD dual('ABC'&RowNo(),RowNo()) as FIELD, RowNo()+1 as VALUE AutoGenerate (5000000);

    Numeric search is much faster, even with 2 conditions!


    Regards Robin


    (Feel free to add comments.)