8 Replies Latest reply: May 15, 2014 10:05 AM by Olly Bond RSS

    Creating a Filter

    stc _

      I have a column that contains two numbers in the following format 17,14.  I am trying to create a filter expression that looks for the number 17.  Is there a way that I can write an expression that will filter anything that that contains the number 17?  I have looked at all of the operators and can't seem to find it.

      Thanks.

        • Creating a Filter
          Olly Bond

          Hello STC,

           

          I'm pretty sure that INT() will return the integer value of a numeric expression:

           

          int(field)=17

           

          HTH

           

          Olly

            • Creating a Filter
              Data Kruncher

              STC,

               

              If I'm interpreting this correctly, you've got a Character type field that contains the value "17,14".

               

              To filter all instances where 17 exists in MyField, use this as the filter expression:

              InStr("17",MyField)>0

              /CODE

                • Creating a Filter
                  stc _

                  Thank you InStr("17",MyField)>0 worked.

                    • Creating a Filter
                      stc _

                      I discovered a problem with this filter.  I have a column that contains 3, 30, 33.  I am using InStr("3",MyField)>0 but my filter is returning anything that has a 3 and I do not want this. How can I get just the column that has 3 in it?

                        • Creating a Filter
                          Data Kruncher

                          Assuming:

                          that the values will always be listed in order within the field, and

                          that this is a Character field, and

                          that there are spaces after each comma,

                          /LISTthen this forumla will find the instances that contain 3's:

                           

                          InStr(" 3,"," "+trim(MyField))>0 .Or. Right(MyField,2)=" 3" .Or. MyField="3"

                          /code

                           

                          The formula adds a space to MyField just in case the value is something like "3, 30, 33". This ensures that it'll still work with "1, 2, 3, 4" or "1, 2, 3" or "3".

                           

                          Kruncher

                            • Creating a Filter
                              stc _

                              No, the values are not always listed in order in the same field.

                              They are not character fields, they are numeric

                              There aren;t any spaces after the comma.

                               

                              The file looks like this

                               

                              Name           Group

                              mary smit         3

                              john doe         33

                              mary jane       32

                                • Creating a Filter
                                  Grant Perkins

                                  No, the values are not always listed in order in the same field.

                                  They are not character fields, they are numeric

                                  There aren;t any spaces after the comma.

                                   

                                  The file looks like this

                                   

                                  Name Group

                                  mary smit 3

                                  john doe 33

                                  mary jane 32[/quote]

                                   

                                  Is the filter that deals with the 17,14 column also having to deal with the examples above? I would guess not if it is numeric unless you are using a comma as a decimal point in the first example.

                                   

                                  The example data above would, I think, be best dealt with using a numeric field based filter. So if you want to filter for only 3 (to use an example) make it a Value and filter as numberfield=3  .

                                   

                                  You can also create a filter for a selection (or a range) of numbers but if you have a lot of non-contiguous numbers to select for I would be tempted to go the flexible route and define the commonly used ones individually and then use the Compound Filter facility to bring them together as you need them to group them.

                                   

                                  All in all there are a number of different usable ways to write the filter formulas. Which you use may be dictated by the requirement or may just be a matter of personal choice if there options available to you.

                                   

                                  As I recall there are some rather useful suggestions in the Help file. I found them to be excellent guides before I became familiar with Monarch. I still use them from time to time if something out of the ordinary is required.

                                   

                                  HTH.

                                   

                                   

                                  Grant

                                    • Creating a Filter
                                      Olly Bond

                                      Hello STC,

                                       

                                      A different approach to the problem might be to have one row per entry. Rather than:

                                       

                                      NAME        GROUP

                                      Bill        3,17,42

                                      Henry       2,3,19

                                      /CODE

                                       

                                      resulting in two rows in your table, and having to do some fiddly work to split out the GROUP values and apply logical tests to them, you could, provided you know an upper bound on the maximum number of group values for each record (10?, 100?), easily create a table of:

                                       

                                      NAME        GROUP

                                      Bill        3

                                      Bill        17

                                      Bill        42

                                      Henry       2

                                      Henry       3

                                      Henry       19

                                      /CODE

                                       

                                      One technique for achieving this in one pass would be using the multi column region option, but with a twist. You can then manipulate the data on a line by line basis, filtering or adding calculating fields as required, and if you want the output back in the original layout, easily achieve this with a summary using GROUP as an across key.

                                       

                                      HTH,

                                       

                                      Olly