IBM Security i2

Expand all | Collapse all

Remove zeros on the left

  • 1.  Remove zeros on the left

    Posted 20 days ago
    I have a problem that would like your help.
    
    I have a list of account numbers, which may vary in size, for example account No. 123456, No. 1254, No. 12545877.
    
    However, the file that has these data comes with account numbers, but has zeros on the left, which can vary in quantity, for example, it can come in this way, 000123456 or 00123456 or 0000123456.
    
    I would like to know how to remove these zeros on the left without having to use "remove prefix" as the number of zeros varies.
    
    I also have to be careful when removing these zeros as I have account numbers that in the middle can have zero, for example 0001230004

    Some examples for acount numbers:

    000123456

    001234560

    00000120456

    001230004587

    0006540002455

    00012540000

    Any suggestion


    ------------------------------
    Paulo Ribeiro
    ------------------------------


  • 2.  RE: Remove zeros on the left

    Posted 20 days ago
    Hi Paul,

    Which i2 product are you using?  For instance, are you doing an excel spreadsheet or csv import within i2 iBase or Analyst's Notebook?  

    Thank you,
    Linda

    ------------------------------
    Linda Miyoshi
    ------------------------------



  • 3.  RE: Remove zeros on the left

    Posted 20 days ago
    It is for an import in ibase but sometimes to import to analyst notebook, and the data is in a excell spreadsheet

    ------------------------------
    Paulo Ribeiro
    ------------------------------



  • 4.  RE: Remove zeros on the left

    Posted 20 days ago
    I didn't see anything very elegant with a quick search and a little testing.

    I did find that I can get the desired results with multiple "remove prefix".  Assuming there is a maximum number of leading zeroes possible, you can do:
    Remove prefix "0"
    Remove prefix "00"
    Remove prefix "000"
    Remove prefix "0000"
    Remove prefix "00000"

    It's ugly, but you can save it in your import spec and only do it once.
    Hopefully someone else has a nicer solution.



    ------------------------------
    Linda Miyoshi
    ------------------------------



  • 5.  RE: Remove zeros on the left

    Posted 20 days ago
    Edited by Linda Miyoshi 20 days ago
    Alternatively, you can format the column in the spreadsheet prior to import.

    ------------------------------
    Linda Miyoshi
    ------------------------------



  • 6.  RE: Remove zeros on the left

    Posted 19 days ago
    Hi

    The solution proposed above is the right idea - but I think the problem will be that each action acts before the next one. So if you have date like this:

    123456
    0123456
    00123456
    000123456

    etc
    then Remove Prefix 0 will give:

    123456
    0123456
    00123456
    000123456

    then the next suggested remove prefix 00 will give:

    123456
    0123456
    123456
    0123456

    etc

    I think you can just use Remove Prefix 0 and copy and paste that action as many times as you need - I just tried it and it took about 10 secs to resolve. Not elegant but it works!

    Cheers, Mark

    ------------------------------
    Mark Fleet
    Shortest Path Training
    ------------------------------



  • 7.  RE: Remove zeros on the left

    Posted 19 days ago

    Morning all

    Having read the replies from Mark & Linda, here are a couple of extra thoughts.

     1) Mark is correct that running

    Remove prefix "0"
    Remove prefix "00"
    Remove prefix "000"
    Remove prefix "0000"
    Remove prefix "00000"

    will not work properly, but flipping the arguments round so they are
    Remove prefix "00000"
    Remove prefix "0000"
    Remove prefix "000"
    Remove prefix "00"
    Remove prefix "0"
    should do the job as it will remove the largest first.

    2) Linda suggests cleaning it up in the spreadsheet first.  Much as I personally like the Importer tools in ANB, when training ANB I do recommend to the delegates to get it looking how they want in Excel first.  This is because quite a few users are intimidated by an Import proces, and therefore by having it looking right in Excel it is one less thing that can feasibly go wrong during an Import.

    The issue you will have is that the account numbers all have a different number of digits (but see point 3 below), so you cannot use a RIGHT function alone; and because 0s appear within the main account number you cannot use a SUBSTITUTE function by itself.

    There is a nested formula you could write, which would need:
    - An IF function at the start, to determine whether the first character in the text string is a '0'.  This will need to nest...
    - A LEFT function to determine if the string starts with a '0'
    Then if True...
    - apply a SUBSTITUTE function to remove just the first "0" from the text string.  This will prevent 0s in the middle of the account number being deleted
    Then if False
    - just make it the same as the original cell.

    Assuming the list of account numbers is in Column A, this is the formula for the first row

    =IF(LEFT(A1,1)="0",SUBSTITUTE(A1,"0","",1),A1)

    Now you would need to run this multiple times to remove all the 0's, but that's simple because you'll note I didn't use an absolute reference when pointing to cell A1 (i.e. not $A$1).  You just drag the formula down, and then to the right until all the 00s have gone.

    I did this with your examples, and the 0s were all gone by column F.  Copy > Paste Values to your import table and job done. 

    000123456 00123456 0123456 123456 123456 123456
    001234560 01234560 1234560 1234560 1234560 1234560
    00000120456 0000120456 000120456 00120456 0120456 120456
    001230004587 01230004587 1230004587 1230004587 1230004587 1230004587
    0006540002455 006540002455 06540002455 6540002455 6540002455 6540002455
    00012540000 0012540000 012540000 12540000 12540000 12540000

      

    3) Perhaps tackle this a different way.  Why not have a standard text string length of Account number, and only delete the 0's that precede that.  The longest number in your dummy data is 10 digits long once you crop the leading 0s.  This way you can use an =RIGHT([cell ref],10) formula. 

    We do this on for a particular reference number in our iBase build; the original number can be 1-4 digits long, but needs to referenced by another lookup table, so we made them all 4 digits long and added preceding 0s to make the data consistent.   

    I hope one of these works for you



    Ant



    ------------------------------
    Anthony Patamia
    ------------------------------



  • 8.  RE: Remove zeros on the left

    Posted 19 days ago
    Edited by Anthony Patamia 19 days ago
    An addendum to point 3 and making the Account Numbers a standard length.  This can be done very easily beforehand in Excel too; it requires 2 just steps.

    Using your same example data - where the longest full Account number is 10 digits long, this would be the process.  Obviously it'll need adapting to whatever the true longest number is.

    Step 1: use CONCATENATE to add some preceding 0s so that there are sufficient in place.  Given the standard length here will be 10, then adding 9x 0s should suffice.

    Again assuming the data is in column A, one method of writing the formula is
    =CONCATENATE("000000000",A1)

    Step 2: use RIGHT to extract the last 10 digits from the new column B.  I.e.
    =RIGHT(B1,10)

    Using your data again, it looks like this
    000123456 000000000000123456 0000123456
    001234560 000000000001234560 0001234560
    00000120456 00000000000000120456 0000120456
    001230004587 000000000001230004587 1230004587
    0006540002455 0000000000006540002455 6540002455
    00012540000 00000000000012540000 0012540000


    ------------------------------
    Anthony Patamia
    ------------------------------



  • 9.  RE: Remove zeros on the left

    Posted 19 days ago
    Could you not simply import the account numbers as a number data type? That would automatically remove zeros from the start of a number

    Kind regards

    Charlie


    ------------------------------
    Charlie Hawco
    ------------------------------



  • 10.  RE: Remove zeros on the left

    Posted 19 days ago
    How can i Change the data to number data when import to analyst notebook?





  • 11.  RE: Remove zeros on the left

    Posted 18 days ago
    Morning Paolo

    If you import that detail into the Identity and/or Label of an Entity in ANB, then it is populated as text format.

    If you have something else that could be used as the Identity (e.g. Account name), you could assign that column of data in the table to be an Attribute; one of the Attribute formats is Number. 

    I've just had an experiment with these same example numbers and although it works in terms of dropping the leading 0s, it adds commas and decimal places.  Removing the decimal places is easy within Chart Properties > Attribute Classes > Properties [for that Attribute], I cannot see any setting that removes the commas, so e.g. Account 1230004587 has a number Attribute of 1,230,004,587.
    --------------------------------------------------------------------------------------------------------------------------------------------

    Charlie might be onto something with his/her suggestion though, but just earlier in the process. 

    If your data is holding leading 0s in Excel, it will be because that column of data is in Text format.  Move the data into a new column in Excel in Number format.  There are a few ways of doing this, and they are mostly easier than the nested formula I provided yesterday.  There's lots of advice on the web; here's just one webpage advising of 5 methods:
    https://helpdeskgeek.com/office-tips/5-ways-to-convert-text-to-numbers-in-excel/

    If the original data is not in Excel format but e.g. TXT format, you can open the data in Excel in a controlled manner using the relevant option within Data Tab > Get External Data cluster.  The wizard will prompt you how each column of data needs to be treated.

    ------------------------------
    Anthony Patamia
    ------------------------------