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.)