Take n characters from right in string with zeroes on the left

[Federicofkt]

I have a field of type char with strings, most of them representing numbers with some zeroes on the left.

If I just load the field it works fine but I do something like this:
If(Left(tax_code, 5) = '00000', Right(Text(tax_code), 11), tax_code) as tax_code

It just delete all the zeroes on the left.

I've tried building a check like so:

If(Left(tax_code, 5) = '00000', 'Y', 'N') as Check

and it works fine, so the problem is in the Right function.

I've tried with RIght(Text(Num and other stuff found on the community, but nothing seems to work

[mshann01]

If you want to strip out the leading zeroes but keep the field as text, you can use something like this:

Text(Num(tax_code))

I'm not sure why you would have problems with the Right function as it seems to work as expected for me.  The only thing odd I see is that your Left(tax_code,5) isn't forced to Text but the right side is.  I don't think you'd need to force to text using the Right function as it's assumed tax_code is text since it has leading zeroes.

[diegozecchini]

Hi! the Right() function does not preserve leading zeros when dealing with numeric data types. It appears that when you apply the Text() function, Qlik might be converting the result to a numeric format internally, which causes the leading zeros to be removed.

To preserve leading zeros when using Right(), ensure that you're treating the value as a string from start to finish. Here's how you can modify your expression to maintain the leading zeros:

If(Left(tax_code, 5) = '00000', Right(Repeat('0', 11) & tax_code, 11), tax_code) as tax_code

Explanation:
Repeat('0', 11) creates a string of 11 zeros.
By concatenating (&) this string with the tax_code, you're ensuring that there will always be at least 11 characters, including leading zeros if necessary.
The Right() function will then extract the rightmost 11 characters, which includes the original value with the zeros preserved.
This should keep the leading zeros intact while allowing you to handle the field correctly.

Let me know if this works for your case!

[kismoflans]

@mshann01, that is true.

I was curious and I have tried to simulate close to the same thing and works fine from my end. See the attached fake data set.

If you load the same, it clips the same based on the condition. 

```LOAD tax_code,
If(Left(tax_code, 5) = '00000', Right(Text(tax_code), 11), tax_code) as tax_code2,
len(tax_code) as length,
If(Left(tax_code, 5) = '00000', 'Y', 'N') as Check
FROM
[..\Downloads\Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);```

My thinking could be there probably is more to the data format.

I recommend the below;- 

a) Force the results of the conditions to text just in case there are leading zeros after the len trim

```If(Left(tax_code, 5) = '00000', Right(Text(tax_code), 11), Text(tax_code)) as tax_code```

b) Employ mid function by extracting a substring 

```If(Left(tax_code, 5) = '00000', Mid(tax_code, Len(tax_code) - 10, 11), tax_code) as tax_code```

If it doesn't work, kindly share sample data on the part not working as expected.

[krishn5]

Not sure why right() to working, just try using MId() function once.