Adding comma's to an amount field

[nagadastagirireddy]

Hi

I have a requirement to add comma's to an amount field. Please suggest how to do this.

Source: Sequential file
Target: DB2 table

Example:
-------------
Input: 10000000000
Expected output: 10,00,00,00,000

Thank you

[ArndW]

You would like to output a number in "lakh" format, is that what you are looking for? The comma separators aren't used in numeric fields, just when converting to a display (output) field. There is no builtin code to do this, but it is possible to use either a string conversion in a parallel job or BASIC-Transform "FORMAT" command to do this.

[nagadastagirireddy]

Hi ArndW,

Yes, we need to represent data in 'lakh' format. Agreed with you we can't store comma separators in numeric field.

Right now my project requirements are not clear, we have got very high level details of transformations that we might need to apply on the input data, this is one among them.

Can you please suggest using which string conversion functions we can do this in parallel jobs.

Thank you

[ray.wurlod]

There is nothing available out of the box for parallel jobs to display numeric data as strings using lakh format.

You will need to use either a BASIC Transformer stage, or a server shared container containing a Transformer stage, or indeed a server job containing a Transformer stage, to effect this re-formatting.

You should be able to use the Fmt() function, as Arnd suggested. Or you could create a straightforward DataStage BASIC routine to perform the equivalent. Depending on your range of values you may need more than one Fmt() function, governed by a nested If..Then..Else construct.

A particularly advanced ( = ancient) UniVerse programmer could even create a "user exit" so that you could use Oconv() and Iconv() functions with lakh format.

[ray.wurlod]

The following DataStage BASIC routine shows one algorithm that can easily be used to convert a string comprised entirely of numeric characters into a "lakh format" string. You could perhaps adapt it into C++ for use in a Build stage or a parallel Routine.

Code: Select all

FUNCTION LakhFormat(aNumber)

      If Unassigned(aNumber) Or IsNull(aNumber)
      Then

         Ans = @NULL

      End
      Else

         * Argument must consist of only numeric characters.
         If aNumber Matches "1N0N"
         Then

            Ans = Right(aNumber,3)
            MaxPos = Len(aNumber) - 3
            For i = MaxPos To 1 Step -2
               Ans = "," : Ans
               Ans = aNumber[i,1] : Ans
               If i > 1 Then Ans = aNumber[i-1,1] : Ans
            Next i

         End
         Else

            Ans = aNumber
            Msg = "Argument is not comprised entirely of numeric digits."
            Call DSLogWarn(Msg, "LakhFormat")

         End

      End

RETURN(Ans)

[SURA]

What is the target data type ?

[nagadastagirireddy]

Hi SURA,

Even I am not clear at this point of time what will be the target and its data type. I am guessing it could be varchar as numeric/decimal data types don't support this format.

Thank you

[qt_ky]

Ray, thanks for posting the routine. It's nice to be aware of so many options available!

[SURA]

The intention of my question is about the data and the validation around the data, if it is going to be other than numeric or decimal.

The solution (routine) is already in forum or while read SQL can do the same.

[ray.wurlod]

The target must be string of some kind. I took that as a given when the original poster asked about inserting commas, which are not numeric characters.

[ray.wurlod]

I have extended the routine so that it handles signed numbers, decimal places, and optional currency prefix of "Rs" or "$". The essentials of the code remain unchanged.

Code: Select all

FUNCTION LakhFormat(aNumber)

     If Unassigned(aNumber) Or IsNull(aNumber)
      Then

         Ans = @NULL

      End
      Else

         vNumber = aNumber
         CurrencyPrefix = ""
         DecimalSuffix = ""

         If aNumber Matches "0X'.'0N"
         Then
            DecimalSuffix = "." : Field(aNumber, ".", 2, 1)
         End

         If aNumber Matches "'Rs'0X"
         Then
            CurrencyPrefix = Left(aNumber,2)
            vNumber = Trim(Right(aNumber, Len(aNumber)-2))
         End

         If aNumber Matches "'$'0X"
         Then
            CurrencyPrefix = Left(aNumber,1)
            vNumber = Trim(Right(aNumber, Len(aNumber)-1))
         End

         If Len(DecimalSuffix) Then vNumber = Field(vNumber, ".", 1, 1)

         If vNumber Matches "1N0N" Or vNumber Matches "'-'1N0N" Or vNumber Matches "'+'1N0N"
         Then

            Sign = ""
            iNumber = vNumber
            If Left(vNumber,1) = "-" Or Left(vNumber,1) = "+"
            Then
               Sign = Left(vNumber,1)
               iNumber = Matchfield(vNumber, "1X0N", 2)
            End

            Ans = Right(iNumber,3)
            MaxPos = Len(iNumber) - 3
            For i = MaxPos To 1 Step -2
               Ans = "," : Ans
               Ans = iNumber[i,1] : Ans
               If i > 1 Then Ans = iNumber[i-1,1] : Ans
            Next i

            If Len(CurrencyPrefix) Then Ans = CurrencyPrefix : Ans
            If Len(Sign) Then Ans = Sign : Ans
            If Len(DecimalSuffix) Then Ans := DecimalSuffix

         End
         Else

            Ans = aNumber
            Msg = "Argument is not comprised entirely of numeric digits."
            Call DSLogWarn(Msg, "LakhFormat")

         End

      End

RETURN(Ans)