6 Replies Latest reply: May 15, 2014 9:53 AM by nikki _ RSS

    Extract certain columns with different data

    nikki _

      From the data below, I only need the lines that contain the numbers 10, 12, 15, 29, 22, and 11 in the third and fourth position of the Column 4 (C4). 

       

      For example, I would only need to extract lines 1-6, not Lines 7 & 8.

       

      How can I do this?

       

       

      Sample Data:

             C1     C2  C3  C4                C5

      Line 1 1030   2   1 0 0G10103004010030  1    

      Line 2 1004   34  1 0 0B12100489010000  1   

      Line 3 1004   34  1 0 0B15100489010000  1  

      Line 4 9986   75  3 0 9229998606010000  1   

      Line 5 9990       1 0 0C22999095010000  1  

      Line 6 1771       2 0 7411177106010000  1

      Line 7 1803         0 0518038519000004  1

      Line 8 1030   2   1 0 0G17103004010030  1

        • Extract certain columns with different data
          Grant Perkins

          Originally posted by nikki:

          Sample Data:

          [font="courier"]       C1     C2  C3  C4                C5

          Line 1 1030   2   1 0 0G10103004010030  1    

          Line 2 1004   34  1 0 0B12100489010000  1   

          Line 3 1004   34  1 0 0B15100489010000  1  

          Line 4 9986   75  3 0 9229998606010000  1   

          Line 5 9990       1 0 0C22999095010000  1  

          Line 6 1771       2 0 7411177106010000  1

          Line 7 1803         0 0518038519000004  1

          Line 8 1030   2   1 0 0G17103004010030  1 /font[/quote][/b][/quote]Nikki,

           

          The option seems to be to extract all of the lines and then use a filter on the table for the 2 character positions that identify whether a record is required or not.

           

          Are you familiar with filters?

           

          Are you splitting up the data string into separate fields as part of the model or is it extracted as one long string?

           

          Are the six numbers you listed the only ones you need OR examples of the only ones you need from that report sample BUT there could be many more numbers that SHOULD be selected if they occur?

           

          There are a number of ways to approach the definition and use of filters - hence the questions so that we can identify the most likely useful approach for your needs here rather than work through all of them!

           

          HTH.

           

          Grant

          • Extract certain columns with different data
            Data Kruncher

            Hi nikki,

             

            Assuming that your character field C5 in line 1 is "0G10103004010030", one option would be for you to define a filter with the formula as follows:

            [font="courier"]Val(Substr(C5,3,2))  .In.(10,11,12,15,22,29)  /font[/quote]HTH,

            Kruncher

            • Extract certain columns with different data
              nikki _

              No. I'm a novice when it comes to Monarch.  I've never used the filter feature.

               

              I'm splitting the 16 character string into four smaller pieces.  Example, 0G10103004010030, the first two digits means one thing, the next two digits mean something else, the next four digits means something else, etc.

               

              The six numbers I listed are the only ones I need.

               

              I would like the most efficent way because the report is over a 1,500 pages.

               

              As a result of my answers about, will filters be me most likely option?

              • Extract certain columns with different data
                nikki _

                Originally posted by Data Kruncher:

                Hi nikki,

                 

                Assuming that your character field C5 in line 1 is "0G10103004010030", one option would be for you to define a filter with the formula as follows:

                [font="courier"]Val(Substr(C5,3,2))  .In.(10,11,12,15,22,29)  /font[/quote]HTH,

                Kruncher /b[/quote]I tried this and it worked!

                 

                But, I was thinking is there any way to avoid even extracting all the detail data then appling a filter.  The reason that I ask this is because the report is just so large, over 1,500 pages.

                 

                If not, I will use the filter option that you both have advised.

                 

                Thanks!

                • Extract certain columns with different data
                  Grant Perkins

                  Filters definitely the way to go and based on your answers we can pick a very appropriate option here.

                   

                  So you have aleady separated the 2 character field you need to filter by. I will assume it is a numeric field for now. I will call it FILTERFIELD  - you can replace that with your field name in the filter.

                   

                  From the Table window (or a number of other places if you know them) go to the Data menu, then Filters, Click on the NEW button and select Formula-based.

                   

                  You will see a window that offers you a list of existing fields, some Operators and on the right some functions. Some very powerful filters can be defined here but the one you need at the moment is quite simple. The filter will be inclusive - we tell Monarch what we want to include.

                   

                  The formula for the filter is defined in the Expression window.

                   

                  From the list of fields double click on your 2 digit "FILTERFIELD". Monarch will place thin field name in the Expression window.

                   

                  Now from the 'Operators' window double click on the '.IN.()' operator. In the parenthesis enter the values of the field you wish to include with a comma between the values.

                   

                  So if the field is a numeric field the filter formula would be:

                   

                  FILTERFIELD.IN.(10, 12, 15, 29, 22, 11)

                   

                  If your 2 character field is Alphanumeric you have to must the values in "" marks.

                   

                  FILTERFIELD.IN.("10", "12", "15", "29", "22", "11")

                   

                  Save that and see what happens.

                   

                  It is worth reading the Help information related to filtrs in order to gain some information about what is possible in preparation for the next time a filter will be useful. (Quite often I find.)

                   

                  Also a section on the Help file called Creating Expressions which explains how to use the Operators and what they can do for you.

                   

                  Some filter can become quite complex. In which case often it is easier to define sepearet simple filters (also easier to test that they work as required!) and then use the Compound Filter option to build one complex filter from two or more simpler ones. (I mention that mainly in case you were wondering what is the difference between a formula-based and a Compound filter.)

                   

                  HTH.

                   

                  Grant.

                   

                  Edit to add:

                   

                  Ah, didn't see Kruncher's reply as I went straight to nikki's previous last response and then we cross posted! :-|

                   

                  Don't worry about the number of pages - the amount of work for Monarch is the same if you are working from a report. All the lines will have to be read and interpreted anyway. (It might make a difference to some operations if you were working with a database.)

                  • Extract certain columns with different data
                    nikki _

                    Originally posted by Grant Perkins:

                    Filters definitely the way to go and based on your answers we can pick a very appropriate option here.

                     

                    So you have aleady separated the 2 character field you need to filter by. I will assume it is a numeric field for now. I will call it FILTERFIELD  - you can replace that with your field name in the filter.

                     

                    From the Table window (or a number of other places if you know them) go to the Data menu, then Filters, Click on the NEW button and select Formula-based.

                     

                    You will see a window that offers you a list of existing fields, some Operators and on the right some functions. Some very powerful filters can be defined here but the one you need at the moment is quite simple. The filter will be inclusive - we tell Monarch what we want to include.

                     

                    The formula for the filter is defined in the Expression window.

                     

                    From the list of fields double click on your 2 digit "FILTERFIELD". Monarch will place thin field name in the Expression window.

                     

                    Now from the 'Operators' window double click on the '.IN.()' operator. In the parenthesis enter the values of the field you wish to include with a comma between the values.

                     

                    So if the field is a numeric field the filter formula would be:

                     

                    FILTERFIELD.IN.(10, 12, 15, 29, 22, 11)

                     

                    If your 2 character field is Alphanumeric you have to must the values in "" marks.

                     

                    FILTERFIELD.IN.("10", "12", "15", "29", "22", "11")

                     

                    Save that and see what happens.

                     

                    It is worth reading the Help information related to filtrs in order to gain some information about what is possible in preparation for the next time a filter will be useful. (Quite often I find.)

                     

                    Also a section on the Help file called Creating Expressions which explains how to use the Operators and what they can do for you.

                     

                    Some filter can become quite complex. In which case often it is easier to define sepearet simple filters (also easier to test that they work as required!) and then use the Compound Filter option to build one complex filter from two or more simpler ones. (I mention that mainly in case you were wondering what is the difference between a formula-based and a Compound filter.)

                     

                    HTH.

                     

                    Grant.

                     

                    Edit to add:

                     

                    Ah, didn't see Kruncher's reply as I went straight to nikki's previous last response and then we cross posted! :-|

                     

                    Don't worry about the number of pages - the amount of work for Monarch is the same if you are working from a report. All the lines will have to be read and interpreted anyway. (It might make a difference to some operations if you were working with a database.) /b[/quote]All of you are GREAT!!!

                     

                    This was extremely helpful!