Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
robert2012
New Contributor III

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
MVP
MVP

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

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

5 Replies
MVP
MVP

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

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

robert2012
New Contributor III

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

Wow, so simple and powerful!!!

Thank you very much! You made my day :-)

robert2012
New Contributor III

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

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!

MVP
MVP

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

Yes, use it this way:

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

- Ralf

robert2012
New Contributor III

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

Great! Thanks a lot!

Community Browser