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

    Column combine with formating via calculated field?

    tbrad _


      I have a report that produces as follows:

      Account               Unit

      1002093787          1

      1001858453          5

      1001405651          12

      1002079471          0



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







      Can this output be accomplished through a calculated field?     




        • Column combine with formating via calculated field?
          KeyserSoze _

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


          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.








            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!