12 Replies Latest reply: May 15, 2014 10:15 AM by KeyserSoze _ RSS

    Calculated Fields

    DD _

      I'm creating a calculated field by concatenating 5 fields in the table.  Is there a way to keep the integrity of the width of the individual elements.

       

      For example Column A = "Testing" and is 8 characters wide

                         Column B = "this out" and is 8 characters wide.

       

      When I enter the formula for the calculated field if put "a + b" and it is returning me "Testingthis out". I would like it to be "Testing this out". Inserting "blanks" in between each element will not work if a word is 9 characters and spans 2 elements. Then I get words with spaces in them.  I'm assuming that the spaces to the right of data in an element are not really spaces or monarch doesn't recognize them as spaces. In other languages that I use I can pad the data to a specific length and I can either add those characters to the left or right of the that string to achieve a consistent size. Is there a function in Monarch that will allow me to do this? I have looked and thought maybe "stuff" would work but I was unable to achieve the proper results.

       

      Any suggestions?

        • Calculated Fields
          RalphB _

          Hi DD,

           

          Try adding a " " in between each field you are concatenating.  So in your example it would be "a + " " + b".  Give that a try.

            • Calculated Fields
              Olly Bond

              Hello DD,

               

              Experimenting with something like:

               

              if(len(rtrim())=len();[stringA]" ";[stringA]+)

               

              might also help.

               

              Best wishes,

               

              Olly

                • Calculated Fields
                  Grant Perkins

                  Hi DD,

                   

                  The Replace function might work for you assuming that the way the text in the columns is created does not have odd things working with spaces for formatting. (And that three are no alternative ways of extracting the original data to avoid multi-column spread in the first place!)

                   

                  Replace(,"  "," ")

                   

                  should work in theory. This will concatenate 5 columns (fields in the table) and then replace all instances of 2 spaces with one space. (You will need to look carefully at the formula to see the spacing difference between the different quotation mark pairs).

                   

                  If there is something odd with the data content this may not be the full answer but I would pretty much expect it to work with regular text that has been previously broken apart during extraction. It worked when I ran the formula as a test when defining a calculated field using some spacing variants that I thought might logically exist.

                   

                  If not I would be looking for a different extraction approach to make for an easier life maintaining the model further down the road. Probably.

                   

                  HTH.

                   

                   

                  Grant

                  • Calculated Fields
                    DD _

                    Olly, here is the data that I am working with.

                     

                    LOWER LEVEL BATH/FAMILY/BABY WINDOW

                     

                             

                    ColA = " LOWER L"

                    ColB = "EVEL BA"  

                    ColC = "TH/FAMIL"                

                    ColD = "Y/BABY "                 

                    ColE = "WINDOW   "           

                     

                    Column widths are 8,7,8,7,10 respectively

                     

                     

                    When I test out your possible solution on columns A & B this was the result.   " LOWER L EVEL BA    "

                     

                    When I test on column D & E it worked.

                     

                    The replace suggested by Grant did not work either

                     

                     

                    Any Other ideas guys?

                     

                    I appreciate all the help

                      • Calculated Fields
                        Grant Perkins

                        DD,

                         

                        The Replace method works for me. In this case I am testing in V11 but can try V10  - is that what you have?

                         

                        Can you share your exact formula? (Just for completeness.)

                         

                        Are the columns in a table extracted from a Report or a database in the form of a Fixed Length file, a delimited File, Excel or something else?

                         

                         

                        Grant

                          • Calculated Fields
                            DD _

                            Grant, the exact formula I am using is quit complex so to test these formulas and to make sure my formula is not hindering the suggested ones I just created a text file with the following data and just applied your suggested formula to it.

                             

                            "LOWER LEVEL BATH/FAMILY/BABY WINDOW" 

                             

                            Replace(," "," ") 

                             

                            results = " LOWER LEVEL BATH/FAMILY/BABYWINDOW"   (no space between "BABY" AND "WINDOW"

                             

                            Just to remind you here is how I have the table set up.

                             

                            ColA = " LOWER L"

                            ColB = "EVEL BA"

                            ColC = "TH/FAMIL"

                            ColD = "Y/BABY "

                            ColE = "WINDOW "

                             

                            Column widths are 8,7,8,7,10 respectively

                             

                             

                            If you notice "ColD" is the only element that does not fill the entire element. I'm going to assume that Monarch doesn't honor trailing spaces or are recognized as Null but when concatenating those 2 Elements it ends up with "BABYWINDOW".  If I instert a " " in between each element then ColD and ColE turn out fine but the other elements then have space in the middle of the words.

                             

                            Is there not a way to pad a string to a certain length and have to ability to pad it with a specific character and choose whether or not to pad left or right?

                              • Calculated Fields
                                Olly Bond

                                Hello DD,

                                 

                                In v11.5, I've just built a model with calculated fields set to exactly your data, and defined the output as:

                                 

                                intrim(ColAColBColCColDColE)

                                 

                                which gives me:

                                 

                                LOWER LEVEL BATH/FAMILY/BABY WINDOW

                                 

                                The Data Length of the ColA, ColB etc are all set to 20 in my model - what are they in yours?

                                 

                                Best wishes,

                                 

                                Olly

                                  • Calculated Fields
                                    DD _

                                    Olly, the data lengths are as stated earlier in the thread, I increased all of them to 20 like in your model and it still produces the same result "LOWER LEVEL BATH/FAMILY/BABYWINDOW". I am currently running v10.0

                                     

                                    I did experiment with this formula on columns ColD & ColE and was able to produce the results I am looking for for those 2 elements.

                                     

                                    intrim(if(LEN(ColD) < 7, ColD + "       " + ColE,ColD + ColE))

                                     

                                    That just seems like a long ways to go when I need to do that on 5 columns. Looks like I may need to upgrade.    

                                     

                                     

                                    I really appreciate all your help.

                                    • Calculated Fields
                                      Grant Perkins

                                      Hello DD,

                                       

                                      In v11.5, I've just built a model with calculated fields set to exactly your data, and defined the output as:

                                       

                                      intrim(ColAColBColCColDColE)

                                       

                                      which gives me:

                                       

                                      LOWER LEVEL BATH/FAMILY/BABY WINDOW

                                       

                                      The Data Length of the ColA, ColB etc are all set to 20 in my model - what are they in yours?

                                       

                                      Best wishes,

                                       

                                      Olly[/QUOTE]

                                       

                                       

                                      Olly, this is interesting because I can't see a way to prevent the trailing spaces being trimmed by default but if you got the correct result with intrim there must be something different going on. I just can't think what else to try - so will continue to experiment.

                                       

                                      This all harks back to a very very old debate back around V4 iirc.

                                       

                                      Grant

                                        • Calculated Fields
                                          Grant Perkins

                                          V8 has the same behaviour.

                                           

                                          I think by that point people were probably avoiding the issues by extracting text into a single field so it is not a questions that, in this precise form, has arisen much in recent times other than people wishing always to force a space between the fields they are concatenating. That's a different problem of course.

                                           

                                           

                                          Grant

                                            • Calculated Fields
                                              KeyserSoze _

                                              This, though a little more lengthy, seems to do the trick.

                                               

                                              Trim(

                                              If(Right(A;Len(A))=" " .OR. Left(B,1)<>" ";A,A+" ") +

                                              If(Right(B;Len(B))=" " .OR. Left(C,1)<>" ";B,B+" ") +

                                              If(Right(C;Len(C))=" " .OR. Left(D,1)<>" ";C,C+" ") +

                                              If(Right(D;Len(D))=" " .AND. Left(E,1)<>" ";D,D+" ") +

                                              E

                                              )[/CODE]

                                               

                                              Does this resolve the challenge?

                                        • Calculated Fields
                                          Grant Perkins

                                          Hi DD,

                                           

                                          Ok, I see the problem. Seems I didn't check the last 2 fields expecting them to work. Doh!

                                           

                                          Now it gets a little interesting because V11.5 behaviour is the same as V10.

                                           

                                          However in the Options settings there is a tick box that, in theory, enables or disables auto trimming of leading and trailing spaces on input. If you toggle this it works as you might expect for leading spaces but seems to have no effect on trailing space.  Not something I recall in the past but will check back. Obviously it's not something I have had to use much recently - surprised to find it.

                                           

                                          Olly's approach is probably as close as one is likely to get. Except that the imported field has already been trimmed so a LEN check needs to be compared to the known intended length of the field. Then add spaces to fill the balance - or just one space it that would be better than 2 or 3. The Leading spaces should be OK by default (if they are correct in the original data ...)

                                           

                                          It's a pain though. Are you sure there is no other way to get those lines extracted as a single field in the first place?

                                           

                                          If you feel like posting a sample page or two we could see what ideas arise. There are some less than obvious approaches around that might help.

                                           

                                           

                                          Grant