Adding comma's to an amount field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India
Adding comma's to an amount field
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
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
Giri
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India
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
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
Giri
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 15
- Joined: Fri Jan 22, 2010 4:35 am
- Location: India
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.
The solution (routine) is already in forum or while read SQL can do the same.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.