topic Re: Adding zeros to number in QlikView

Adding zeros to number

Fri, 20 Nov 2020 18:02:30 GMT

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

Re: Adding zeros to number

Wed, 25 Apr 2012 18:51:21 GMT

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.

Adding zeros to number

CELAMBARASAN — Wed, 25 Apr 2012 18:54:50 GMT

Hi,

To add zeros use

Num(FieldName,'0000000000') as NumFieldName

To remove zeros use

Num(FieldName) as NumFieldName

Celambarasan

Adding zeros to number

Jason_Michaelides — Wed, 25 Apr 2012 19:04:56 GMT

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

Adding zeros to number

Wed, 25 Apr 2012 19:11:11 GMT

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