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

[Anonymous]

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.

[rbecher · Accepted solution]

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

[Anonymous]

Wow, so simple and powerful!!!

Thank you very much! You made my day 🙂

[Anonymous]

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]

Yes, use it this way:

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

- Ralf

[Anonymous]

Great! Thanks a lot!