SPSS Modeler

 View Only

TOP TIP: How to refer to many fields in any node that uses the Expression Builder without having to click on all of the fields.

  • 1.  TOP TIP: How to refer to many fields in any node that uses the Expression Builder without having to click on all of the fields.

    Posted Tue May 25, 2021 09:21 AM

    Modeler Users, I posted this to an internal IBM Slack Channel recently and thought the rest of the Modeler world might see some benefit from this.

    The issue came up when a Modeler user wanted to select all records where any one of a long list of fields had a T value.  The commonly known method would be to click on each field in the expression builder in the Select Node, then click "or" ultimately ending with ="T".  As in, "If X or Y or Z or AB or AC....or ZZ = "F".  If you have hundreds of fields to act on, then you've got a load of clicks ahead of you.  BUT NOT IF YOU FOLLOW THE METHOD DESCRIBED BELOW!

    What do you do when you have 100+ T/F fields that you want to query for a True value but you don't want to have to type or click your way through the Expression Builder to insert every one of the fields in your code?If you aren't aware of the @FIELDS_BETWEEN() function, you are about to see a huge time-saver!An IBM colleague asked me today how she could use the output from a Text Analytics model that produced many fields with T or F values and to trigger an action if none of them were populated with T.  She didn't want to have to enter every field Category1_TF="F" and Category2_TF = "F" and.... for all of the many fields that her TA model produced.  I don't blame her.The @FIELDS_BETWEEN() function comes to our rescue!  This function produces a list object with every field between the two fields including the two that are listed.  If you had 26 fields named Field_A to Field_Z in your data, you can refer to all of them with @FIELDS_BETWEEN(Field_A,Field_Z).  All fields that appear in the data between these two fields as well as the two fields would be referenced in the resulting list.Ok, so what?  I still have to find a function that uses a list of fields as input.The count_equal(item,list) function does the trick.  (There are others!).Thus, if I want a Select Node or Derive Node to act based on the values of the long list of fields, I can simply code:
    count_equal("T",@FIELDS_BETWEEN('Field_A','Field_Z') and the result will be a count of the number of fields with "T" as their value.  This could be used as a condition in a Select Node, as a value for a Derive Node, or any other place that we find the Expression Builder.Instead of coding a very long list of fields, we can just use @FIELDS_BETWEEN().Now you know!



    ------------------------------
    DAVID A. WEST
    Lead Data Scientist
    IBM Public Sector Center of Excellence
    ------------------------------