Any function to remove the leading zeros
Moderators: chulett, rschirm, roy
Any function to remove the leading zeros
Hi,
I have data like
ColA(Hash) ColB(Src)
2577 , 0002577
13 , 0000013
345 , 0000345
I need to match these col's and based on this condition I need to populate other fields into the target.
Here both col values r same but colB is having some zeros preceding the value. How can I eliminate the zeros?
Note: Here the no. of zeros in all the values are not same
Any answer can be appreciated.
Thanks in advance
I have data like
ColA(Hash) ColB(Src)
2577 , 0002577
13 , 0000013
345 , 0000345
I need to match these col's and based on this condition I need to populate other fields into the target.
Here both col values r same but colB is having some zeros preceding the value. How can I eliminate the zeros?
Note: Here the no. of zeros in all the values are not same
Any answer can be appreciated.
Thanks in advance
Ravi
I often just use (In.ColumnName + 0) to force a conversion from string to integer; or you can explicitly use TRIM(In.ColumnName,'0','L') or any number of format statements, such as FMT(In.ColumnName,'Z')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi Ravij,
Use Format function to get the required result.
FMT("ColB(Src)", "RZ")
Z,When used with the Q format, only the trailing fractional zeros are suppressed, and a 0 exponent is suppressed.
For more information gothru the document.
Regards
kcs
Code: Select all
ColA(Hash) ColB(Src)
2577 , 0002577
13 , 0000013
345 , 0000345
Here both col values r same but colB is having some zeros preceding the value. How can I eliminate the zeros?
Here the no. of zeros in all the values are not same
FMT("ColB(Src)", "RZ")
Z,When used with the Q format, only the trailing fractional zeros are suppressed, and a 0 exponent is suppressed.
For more information gothru the document.
Regards
kcs
Any function to remove the leading zeros
Thanks for all,
And Can I append the zeros for the same problem which was disscussed above instead of removing the zeros?
If there is any function plz let me know.
thnaks in advance
And Can I append the zeros for the same problem which was disscussed above instead of removing the zeros?
If there is any function plz let me know.
thnaks in advance
Ravi
Any function to remove the leading zeros
Sorry
Smalll correction
I want to append the zeros for the ColA. And It should be equal to the ColB' zeros.
thanks
Smalll correction
I want to append the zeros for the ColA. And It should be equal to the ColB' zeros.
thanks
Ravi
ravij,
appending zeroes will change the value of the number. If you wish to format a number with leading zeroes then you should use an explicit FMT command such as FMT(In.ColumnName,'6"0"R') which will make the value "0123" into "000123"
appending zeroes will change the value of the number. If you wish to format a number with leading zeroes then you should use an explicit FMT command such as FMT(In.ColumnName,'6"0"R') which will make the value "0123" into "000123"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Any function to remove the leading zeros
Hi Kumar,
This function is not working properly and its giving empty values(no values in the field)
And my other doubt is the ColA which is having leading zeros is my Hash file. I need to match with it with Source(Seq file) Col. Then Where can I define that function?
thanks in advance.
This function is not working properly and its giving empty values(no values in the field)
The remaining functions which suggested by Arnd working fine.Use Conver(Column,'0','')
-Kumar
And my other doubt is the ColA which is having leading zeros is my Hash file. I need to match with it with Source(Seq file) Col. Then Where can I define that function?
thanks in advance.
Ravi
Hi Ravij,
I think it is Convert("0", "", ColB).
For your other option of matching ColA with ColB.
As Arndw suggested if you use FMT(In.ColumnName,'6"0"R').
you will get fixed length(6) string,since no of zeros in your column is not same you won't get the required result.
Try this out,
Declare a Stage variable to calculate the len(colb).
Pass it in FMT function.
Hope it works
Regards
kcs
Code: Select all
This function is not working properly and its giving empty values(no values in the field)
For your other option of matching ColA with ColB
Code: Select all
And my other doubt is the ColA which is having leading zeros is my Hash file. I need to match with it with Source(Seq file) Col. Then Where can I define that function?
As Arndw suggested if you use FMT(In.ColumnName,'6"0"R').
you will get fixed length(6) string,since no of zeros in your column is not same you won't get the required result.
Try this out,
Declare a Stage variable to calculate the len(colb).
Pass it in FMT function.
Hope it works
Regards
kcs
Any function to remove the leading zeros
Thanks for your suggestions.
I will try n come back.
One doubt: Can I use EReplace Function to eliminate the leading zeros?
thnks in advance
I will try n come back.
One doubt: Can I use EReplace Function to eliminate the leading zeros?
thnks in advance
Ravi
Sorry for all
I am not getting the output when use the function which u suggested in the Key Expressin of Hash file(in XFM). But I am getting the required output by using the following function.
Any other suggestions plz
thanks in advance
I am not getting the output when use the function which u suggested in the Key Expressin of Hash file(in XFM). But I am getting the required output by using the following function.
Why I am not able to get the data by using other functions which u suggested?Ereplace(Space(10-Len(Trim(ColName)))," ","0",-1,1) : Trim(ColName)
Any other suggestions plz
thanks in advance
Ravi
Ravij,
The EREPLACE function will replace all occurrences of the string; so in your case if you do use EREPLACE you will turn "0000200" into "2".
In a DataStage transformation all columns are referred to in derivations in the form {LinkName}.{ColumnName}; since I don't know what your exact names are, I always utilize the vague form of "In.ColumnName" and let you fill in the appropriate values for your job.
The EREPLACE function will replace all occurrences of the string; so in your case if you do use EREPLACE you will turn "0000200" into "2".
In a DataStage transformation all columns are referred to in derivations in the form {LinkName}.{ColumnName}; since I don't know what your exact names are, I always utilize the vague form of "In.ColumnName" and let you fill in the appropriate values for your job.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>