Discussion board where members can get started with QlikView.
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.
Go to Solution.
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
Comex Grupo Ibérica
You can use the left function.
This will give you only first 9 char from left side.
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? :-)
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')
Num(subfield('111003333643-LOR-S1','-',1) ) this will give your desired result.
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.
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)
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!! :-)
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! :-)
Please see the attached file .its working on by replacing '-' with '_'.