Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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.
anitamelbye
New Contributor III

Return value from ordernumber: 11100643-LOR

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

1 Solution

Accepted Solutions

Re: Return value from ordernumber: 11100643-LOR

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

14 Replies

Return value from ordernumber: 11100643-LOR

Hi,

    You can use the left function.

    like

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

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

Regards,

Kaushik Solanki

anitamelbye
New Contributor III

Return value from ordernumber: 11100643-LOR

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

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

anitamelbye
New Contributor III

Return value from ordernumber: 11100643-LOR

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

chauhans85
Esteemed Contributor

Return value from ordernumber: 11100643-LOR

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

thanks

Sunil

Re: Return value from ordernumber: 11100643-LOR

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)

anitamelbye
New Contributor III

Re: Return value from ordernumber: 11100643-LOR

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!! :-)

anitamelbye
New Contributor III

Re: Return value from ordernumber: 11100643-LOR

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! :-)

chauhans85
Esteemed Contributor

Re: Return value from ordernumber: 11100643-LOR

hello

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

thanks

Sunil

Community Browser