3 Replies Latest reply: May 15, 2014 10:13 AM by tbrad _ RSS

    Column combine with formating via calculated field?

    tbrad _

      Hello,

      I have a report that produces as follows:

      Account               Unit

      1002093787          1

      1001858453          5

      1001405651          12

      1002079471          0

      1001729225               

            

      The desired output for each line item would be as follows:     

      1002093787-01     

      1001858453-05     

      1001405651-12     

      1002079471     

      1001729225

       

      Can this output be accomplished through a calculated field?     

       

      Thanks,

      Tom

        • Column combine with formating via calculated field?
          KeyserSoze _

          Yes, this can be accomplished with a single calculated field, using this expression:

          Account+

          If(Unit=0 .Or. IsNull(Unit);"";"-"+Str(Unit;2;0;"0"))[/CODE]

           

          This is a Character field, and I'd defined Account as Character as well.

          • Column combine with formating via calculated field?
            Grant Perkins

            Hi Tom and welcome to the forum.

             

            The answer to you question is yes and there are potentially several ways to get you where you want to be. Which is best depends on what you have so far.

             

            Are the two columns in separate fields whan you extract them or a single field? If separate, are you extracting them as numeric or character? You may as well go with character I guess since that is what you well need to end up with.

             

            If separate fields and character type then you only need to mess with the second field for format along the lines of checking for a value and setting 0 to blank (space), leaving any field of LEN() 2 characters as they are and any with LEN() 1 character to a new value that concatenates a 0 (zero) at the front.

             

            Once you have the second column values as you want them you would simply combine the two fields with another concatenation

             

            "-"

             

            in a suitably sized calculated character field to get the combined field BUT skipping the concatenation if the second part is empty.

             

            So something like

             

            IF(=" ", Field1,("-"))

             

            The first part of the puzzle - adjusting the secnd field - is also likely to be a 'nest IF' type formula but just a little more selective and nested than the final combination.

             

            If you are familiar with nest IF formulas I probably don't need to be too specific about that part but I'll come up with something if you need an worked example.

             

            HTH.

             

             

            Grant

             

             

            ETA: I see Mr. Soze has replied at about the time I started my response ...

             

            If you have extracted the second field (Unit) as numeric you will need the STR function but if it is character already then you need to check the content for a specific character (0) or the number of characters (if 1 character add the oleading 0 if two leave it as it is). The choice of approach is yours and there are other approaches too, though they all end up a the same place of course!