Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Add "0" to get the same type of numbers in the load from xls

Hi all,

I have the problem, that I have to different types of numbers in a xls.

Type1: de1234

Type2: de123

I would like to change Type2 with an "0" after the "de" in order to look like Type1: de0123.

Do you have any ideas?

Thank you very much for your answers.

1 Solution

Accepted Solutions
rbecher
MVP
MVP

Hi Robert,

maybe with string operations like this:

LOAD F1, left(F1, 2) & right('0000' & mid(F1,3), 4) as Type;

LOAD * INLINE [

    F1

    de1234

    de123

];

- Ralf

Astrato.io Head of R&D

View solution in original post

5 Replies
rbecher
MVP
MVP

Hi Robert,

maybe with string operations like this:

LOAD F1, left(F1, 2) & right('0000' & mid(F1,3), 4) as Type;

LOAD * INLINE [

    F1

    de1234

    de123

];

- Ralf

Astrato.io Head of R&D
Anonymous
Not applicable
Author

Wow, so simple and powerful!!!

Thank you very much! You made my day 🙂

Anonymous
Not applicable
Author

ok, now there is another problem:

there are also other numbers in the same field, that I don't want to change.

The start like 123456 => they don't have 'de' in the beginning.

Can I use the if-function to check/change only the "de-beginning" numbers?

like:

Load

*,

if (F1 like 'de*', left(F1, 2) & right('0000' & mid(F1,3), 4) as Type; Type)

second Type not to be changed.

Thank you very much your held and answer!

rbecher
MVP
MVP

Yes, use it this way:

if(left(F1,2)='de', left(F1, 2) & right('0000' & mid(F1,3), 4), F1) as ...

- Ralf

Astrato.io Head of R&D
Anonymous
Not applicable
Author

Great! Thanks a lot!