3 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    All Numbers or All Letters

    Hobbes _

      Hi,

       

      In a calculated field, how do I reference all numbers or all letters (a generic reference that looks for one, or a combo of any of them)? 

      For example, if I have 20 date entries, in various positions in the line, and all different dates, how do I set a calculated field to grab all of them?

       

      I've tried {XX/XX/XX}, but it doesn't work.  Is there a way to also reference an alpha position in a calculated field?  I know how to do both of these functions in the report view, but I'm looking for the equivalent functionality in the table view.

       

      Thank you very much for your help with this!

       

      Hobbes

        • All Numbers or All Letters
          Grant Perkins

          I'm not entirely sure I have interpreted your needs fully but the answers will be somewhere in the Functions available for working with calculated fields.

           

          The downloadable Functions Reference Guide offers more detail in most cases than the Help files.

           

          Assuming you have V7 and considering you row of text with dates, one approach (of many possible) might be to use the STRIP function to remove all other characters EXCEPT numerics and the "/". With luck that would leave you with a single field full of space separated dates.

           

          If you then wished to pick those out into separate fields you couold probably use one of the SPLIT functions - LSPLIT seems the most likely but perhaps RSPLIT would be useful as well - to put the dates into new fields.

           

          I'm not sure I understand you second question.

           

          However the IsAlpha and INSTR functions might be of interest. Or I may have completely missed the point. In which case let me know and I will try to seek an alternative suggestion.

           

           

          Grant

           

           

          Originally posted by Hobbes:

          Hi,

           

          In a calculated field, how do I reference all numbers or all letters (a generic reference that looks for one, or a combo of any of them)? 

          For example, if I have 20 date entries, in various positions in the line, and all different dates, how do I set a calculated field to grab all of them?

           

          I've tried {XX/XX/XX}, but it doesn't work.  Is there a way to also reference an alpha position in a calculated field?  I know how to do both of these functions in the report view, but I'm looking for the equivalent functionality in the table view.

           

          Thank you very much for your help with this!

           

          Hobbes /b[/quote]

          • All Numbers or All Letters
            Hobbes _

            Thank you for your suggestions Grant!  I tried using the STRIP function, but since there are many numbers in the field and sometimes more than one date (in two different formats, but both with the "/"), it didn't work for me.

             

            What I'm looking for is a generic reference to a number or letter.  For example, in the report view, when you're creating your model, you can use the alpha symbol or the numeric symbol to capture only the lines that have a number or letter that matches the model.  I can't establish this option because I am working with many variations of the format of the line I am working with.  So I am trying to replicate this alpha/numeric function, found when creating a model, with a calculated field in the table view which will look for a number or letter or specific format of both {XX/XX/XX}.  This is what I am looking for and cannot find.  Does that make more sense?  And yes, I'm using V7.01 Pro.

             

            Thanks again for your help with this!

            • All Numbers or All Letters
              Grant Perkins

              Pity about the STRIP idea but it was a bit of a long shot to hope that there were no important numeric characters in the rest of the line!

               

              Ok, there are ways to use the available functions to break up a line or field of text BUT the problem may be finding some rules that allow you to do that consistent with a highly variable format possible in each line/field.

               

              I think you will be looking at using some of the functions that Nigel Winton suggested previously (September) and maybe adding a few more to get the results you need.

               

              If you can post a few example lines and an indication of what you need to get out of them I will happily have a look and make some suggestions. It would be good to know what parts of the data you have identified as markers that could be used to identify the characters to be extracted.

               

              Grant

               

              Originally posted by Hobbes:

              Thank you for your suggestions Grant!  I tried using the STRIP function, but since there are many numbers in the field and sometimes more than one date (in two different formats, but both with the "/"), it didn't work for me.

               

              What I'm looking for is a generic reference to a number or letter.  For example, in the report view, when you're creating your model, you can use the alpha symbol or the numeric symbol to capture only the lines that have a number or letter that matches the model.  I can't establish this option because I am working with many variations of the format of the line I am working with.  So I am trying to replicate this alpha/numeric function, found when creating a model, with a calculated field in the table view which will look for a number or letter or specific format of both {XX/XX/XX}.  This is what I am looking for and cannot find.  Does that make more sense?  And yes, I'm using V7.01 Pro.

               

              Thanks again for your help with this! [/b][/quote]