topic Re: Return value from ordernumber: 11100643-LOR in QlikView

Return value from ordernumber: 11100643-LOR

anitamelbye — Tue, 28 Jun 2011 10:05:45 GMT

Hi all!

Can anyone tell me how to return only 11100643 from this ordernumber (11100643-LOR)?

What if the ordernumber is 11100643-LOR-S1, and I still just want it to return 11100643 as my ordernumber?

What do I write?
(I don't want it to be done in the load).

Thanks in advance for any clever suggestion.

Regards, Anita

Return value from ordernumber: 11100643-LOR

kaushiknsolanki — Tue, 28 Jun 2011 10:13:46 GMT

Hi,

You can use the left function.

left('11100643-LOR-S1',8)

This will give you only first 9 char from left side.

Regards,

Kaushik Solanki

Return value from ordernumber: 11100643-LOR

anitamelbye — Tue, 28 Jun 2011 10:16:02 GMT

Thank you, but no, sorry, it will not work.

The number "11100643" is sometimes 9 digits and sometimes 8 and sometimes even up to 15..

Any other suggestions? 🙂

Re: Return value from ordernumber: 11100643-LOR

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 10:24:55 GMT

Hello Anita,

Try with the following:

=KeepChar(Left('111003333643-LOR-S1', Index('111003333643-LOR-S1', '-')), '0123456789')

That should cover all lengths and possibilities, provided the format is always a string of numbers followed by the dash "-" sign and you only want to keep the first part.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Return value from ordernumber: 11100643-LOR

anitamelbye — Tue, 28 Jun 2011 10:30:14 GMT

THANK YOU! How wonderful! THANK YOU! That worked just great.

I see now that even some of the ordernumbers even are devided by _ ,

is it possible to write something like

=KeepChar(Left('111003333643-LOR-S1', Index('111003333643-LOR-S1', '-,_')), '0123456789')

Regards, Anita

Return value from ordernumber: 11100643-LOR

SunilChauhan — Tue, 28 Jun 2011 10:35:13 GMT

Num(subfield('111003333643-LOR-S1','-',1) ) this will give your desired result.

thanks

Sunil

Re: Return value from ordernumber: 11100643-LOR

Miguel_Angel_Baeyens — Tue, 28 Jun 2011 10:37:38 GMT

Hello Anita,

This will do the trick (there are some other ways to do that using conditionals or changing the order of the functions, but this one should work)

=KeepChar(Left('111003333643_LOR-S1', Index(Replace('111003333643_LOR-S1', '_', '-'), '-')), '0123456789')

The first string may have "_" or "-" sign, but the index() function will always get the right position of the character. If there are multiple characters as separators, you can nest them

Replace(Replace(Replace('111003333643_LOR-S1', '_', '-'), '/', '-'), '+', '-')

So any of the characters in the first parameter are replaced by the second string.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: For what it's worth, Sunil's solution will work as well and is cleaner than mine, for example using the following:

SubField(Replace(Replace(Replace('111003333643_LOR-S1', '_', '-'), '/', '-'), '+', '-'), '-', 1)

Re: Return value from ordernumber: 11100643-LOR

anitamelbye — Tue, 28 Jun 2011 10:51:36 GMT

TO SUNIL:

It works when I write '111003336_LOR-S1', but when I replace the number with "PDONRT" (what is the ordernumber), you suggestions fails.. But thanks for responding to my question!! 🙂

Re: Return value from ordernumber: 11100643-LOR

anitamelbye — Tue, 28 Jun 2011 10:55:46 GMT

Miguel:

THANKS once again! It works. I am trying Sunils suggestion, but I cant get it to work. And I tried what you said. But as long as your suggestions works, I am satisfied! 🙂

Re: Return value from ordernumber: 11100643-LOR

SunilChauhan — Tue, 28 Jun 2011 12:39:33 GMT

hello

Please see the attached file .its working on by replacing '-' with '_'.

thanks

Sunil

Re: Return value from ordernumber: 11100643-LOR

its_anandrjs — Tue, 28 Jun 2011 15:27:32 GMT

Hi All,

I have this type of data '111003333643'LOR-S1' which contains ( ' ) this symbol in data. So how can i remove it.

Anand

Re: Return value from ordernumber: 11100643-LOR

nagaiank — Tue, 28 Jun 2011 15:33:44 GMT

You may use Replace function, e.g.

Replace(ordernumber,chr(39),'-')

where order number is the string from which you want to replace ' to -.

The charecter ' is represented by chr(39).

Re: Return value from ordernumber: 11100643-LOR

its_anandrjs — Tue, 28 Jun 2011 15:43:31 GMT

Hi All,

i want only numbers from this '111003333643'LOR-S1' means data should be cleanas 111003333643 only this number after clean.

Anand

Re: Return value from ordernumber: 11100643-LOR

its_anandrjs — Tue, 28 Jun 2011 16:03:09 GMT

Hi Krisnamorthy,

Thanks the code you replyed Replace working in my code. Please tell me how can i found character Chr(Value) for perticular symvols like few ex:- " , . ? ! like ways. Please provide help for that.

Anand

Re: Return value from ordernumber: 11100643-LOR

nagaiank — Tue, 28 Jun 2011 16:15:29 GMT

Hi Anand,

You may get the codes at http://www.asciitable.com/

Krishnamoorthy