Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Adding zeros to number

Hi,

Can you please help?

I have a field with a integer for example 0001234567 coming from a DB, and I have the same data field coming from  a excel spread sheet in the following format 1234567.

How can I add zeros to the left of the number coming form the excel sheet, or how do I take off zeros from the left data coming from the DB before loading the data so these two data fields can correspond to the same thing and be associated.

Some data coming from the Excel sheet in this field, are like 234567, then I would have to add 4 zeros to the begining of the number 0000234567 in order to be associated with the one coming from the DB. The one form the DB always come with a fixed amount of numbers, in this example 10 digits.

I look forward to hearing your suggestions.

Thks a lot.

Jorge

1 Solution

Accepted Solutions
Highlighted
Not applicable

If you wanted to add 0's you could try something like

repeat('0', (10 - len(Field))) & Field as NewField

Then NewField will have the zero padding to fill out length 10 and should unify your results.

View solution in original post

4 Replies
Highlighted
Not applicable

If you wanted to add 0's you could try something like

repeat('0', (10 - len(Field))) & Field as NewField

Then NewField will have the zero padding to fill out length 10 and should unify your results.

View solution in original post

Highlighted

Hi,

     To add zeros use

     Num(FieldName,'0000000000') as NumFieldName

     To remove zeros use

     Num(FieldName) as NumFieldName

Celambarasan

Highlighted
Luminary
Luminary

Do you really need to?  I would have though QV would recognise 0001234567 as the number 1234567 and load it as such.  It would therefore link to the 1234567 from the Excel spreadsheet.

If this is not happening you could try Num(IntegerFieldFromDatabase)  during the load to convert to a number.

If that doesn't work then there is a solution for removing leading zeros here.

Hope this helps,

Jason

Highlighted
Not applicable

Thanks a lot bapperson, that worked. Thanks very much as well for those that participated.