4 Replies Latest reply: May 25, 2016 2:33 PM by Aaron Mandujan RSS

    Putting multiple lines of data on in one column

    Aaron Mandujan

      I need help putting numbers form multiple lines into one column per main group item. I'm using Monarch 13.

       

      Sample Data:

      19   Other therapeutic procedures on eyelids; conjunctiva; cornea
           0801 0802 0809 0820 0821 0822 0823 0824 0825 0831 0832 0833 0834 0835 0836 0837 0838 0841 0842 0843
           0844 0849 0851 0852 0859 0861 0862 0863 0864 0869 0870 0871 0872 0873 0874 0881 0882 0883 0884 0885
           0886 0887 0889 0891 0892 0893 0899 090 0920 0921 0922 0923 093 0941 0942 0943 0944 0949 0951 0952
           0953 0959 096 0971 0972 0973 0981 0982 0983 0991 0999 100 101 1031 1032 1033 1041 1042 1043 1044
           1049 105 106 1091 1099 110 111 1131 1132 1139 1141 1142 1143 1149 1151 1152 1153 1159 1171 1172

           1173 1174 1175 1176 1179 1191 1192 1199 9651 9731                   

       

       

      What I need:

       

      CategoryCat DescripCode

      19

      Other therapeutic0801
      0802
      0809
      0820
      etc.

       

      Where all the numbers are under column 3 (Code).

       

      I can grab all the data and extract it in separate columns but then I have to write code in Excel to pull it all together for one column of a pivot table.

      I would like to do it all in Monarch if possible.

       

      Original file is a text file that doesn't play well with Excel.

        • Re: Putting multiple lines of data on in one column
          Armand Max Olivares

          Hi A MAN,

           

          I'm not too sure I understand the dilemma but from the given sample text you may just create an append template form Category and Cat Descrip, then make the Code fields into fields in a Detail template and you should be good to go.

           

          Hope this helps.

           

           

          Kind Regards,

          Armand

            • Re: Putting multiple lines of data on in one column
              Aaron Mandujan

              Amanda

               

              Thank you for taking a look.

               

              The problem is I'm having trouble pulling out each individual code because the alignment of the codes overlap from line to line throughout the file. I want all the codes to come out in just one column.

               

              0801

              0802

              0803

               

              all the way through

               

              1199

              9651

              9731

               

              What I can get using several formula fields is:

               

              19 Other  0801  0802  0803

              19 Other 1199  9651   9731

               

              Etc. : Multiple rows and columns of numbers not just three columns with

               

              Category  :  Cat Description  :  Code

               

              Thanks again

                • Re: Putting multiple lines of data on in one column
                  Olly Bond

                  Hello amandujan,

                   

                  This is simple in a single Monarch model assuming you don't have more than 100 codes more record. Trap the line with the code as a multi-line detail template and include in the sample the first line of the 0123, 0124 codes.

                   

                  Select the fields you need from the first line as normal. Select the entire line of 0123 etc as one big field called BLOB. Make this a Memo field, and in Advanced Options set it to "end on blank field values" of 1. Now define the MultiColumnRegion as active, with the columns starting in position 150 and each one having a width of 1.

                   

                  Now in the table, define a calculated field called Code as lsplit(intrim(Blob);100;" ";Column()) - you chop the block of text into pieces based on the Column function. For your final export you might find that a summary with Category, Description and Code as a the three key fields will give you the formatting and display control you want.

                   

                  The technique is presented here - it shouldn't take more than five minutes work, but if you need help, our standard charge for model building is USD 750 and we'd be happy to assist. Greenbar Analytics - Invisible Data - now visible

                   

                  Best wishes,

                   

                  Olly