topic Re: Find a string of a specific format within a field in QlikView

Find a string of a specific format within a field

datanibbler — Wed, 22 Oct 2014 11:53:20 GMT

Hi,

how can I search in a field for a string constituting an order_number which I need - to split it out and then see if the rest is usable.

The format is

>> S######## << (the letter S and 8 numbers)

There may be no BLANK inbetween or it might be something else - just this string.

It is in a field together with a lot of other stuff, the whole looks like this

SN 8638294 / 4 Stück / S80140915

Can anyone tell me how I could do this?

Thanks a lot!

Best regards,

DataNibbler

Re: Find a string of a specific format within a field

its_anandrjs — Wed, 22 Oct 2014 11:58:05 GMT

Use Index function for this

From QV Help

index(s1 , s2[ , n])

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Examples:

index( 'abcdefg', 'cd' ) returns 3

index( 'abcdabcd', 'b', 2 ) returns 6

index( 'abcdabcd', 'b', -2 ) returns 2

Re: Find a string of a specific format within a field

its_anandrjs — Wed, 22 Oct 2014 12:00:29 GMT

And try like

Load

If(Index(FieldName,'S########'),1,0) as FieldFlag

From Location;

If having any sample file then provide that one.

Regards,

Anand

Re: Find a string of a specific format within a field

ashfaq_haseeb — Wed, 22 Oct 2014 12:04:56 GMT

try with ? instead

S????????

Regards

ASHFAQ

Re: Find a string of a specific format within a field

datanibbler — Wed, 22 Oct 2014 12:14:23 GMT

Hi all,

what is the Wildcard for one numeric character?

I guess I could use that to specify the format of the string I am looking for.

I have to split that out of the field and see what remains and how I can process that.

Re: Find a string of a specific format within a field

datanibbler — Wed, 22 Oct 2014 12:39:24 GMT

Hi,

if I have found a string matching that format - it begins with the letter S - how can I find out whether this is the 1st, 2nd or 3rd iteration of the letter S - so that I can cut that out of the field?

In the field I am currently working on to test, it is the 3rd - but near everything in that field is unsure - it might be the 1st occurence of that letter.

Thanks a lot!

Best regards,

DataNibbler

Re: Re: Find a string of a specific format within a field

tresB — Wed, 22 Oct 2014 12:43:28 GMT

May be like:

Load
String,
SeparatedString,
If(Left(SeparatedString,1)='S',If(IsNum(Mid(SeparatedString,2)) and Len(SeparatedString)=9, SeparatedString)) as Found;

Load String,
Trim(SubField(String, '/')) as SeparatedString Inline [
String
'SN 8638294 / 4 Stück / S80140915']

PFA

Re: Re: Find a string of a specific format within a field

its_anandrjs — Wed, 22 Oct 2014 12:50:03 GMT

Please share any sample file my concern is if your string is in between the string as you say need to search what is the iteration 1st or 2nd.

Regards,

Anand

Re: Find a string of a specific format within a field

datanibbler — Wed, 22 Oct 2014 12:57:09 GMT

Hi tresesco,

that might help.

I guess SeparatedString is not a function, but just a term that you use.

Still - I can find out if there is a piece consisting of a letter S, followed by 8 digits of any kind - there is no wildcard for numerics that won't find alphanumeric characters, is there?

I can then check if the next digit after that is numeric - that would rule out BLANKs and all kinds of separators - and then cut it out.

I'll try that.

Re: Re: Find a string of a specific format within a field

tresB — Wed, 22 Oct 2014 13:15:13 GMT

No such function. Try looping through the string like:

DataNibbler wrote:

Load
String,
If(Mid(String,iterno(),1)='S',If(IsNum(Mid(String,IterNo()+1,IterNo()+8)), Mid(String,IterNo(),IterNo()+9))) as Found

While iterno()<=Len(String);

Load * Inline [
String
'SN 8638294 / 4 Stück / S80140915']

PFA

Re: Find a string of a specific format within a field

datanibbler — Wed, 22 Oct 2014 13:46:46 GMT

Hi tresesco,

I think now I understand what you were proposing to do.

I guess I don't even have to loop through all the positions in the field if I change the order of those steps a little bit:

- First I will determine, by using the LEN() and Keepchar() functions, how often the letter S is in that field - the
order_number always begins with an S.

- Then I will loop through those positions and

- cut out 9 digits from each of those positions in turn

- check whether the 2nd and 3rd digit in one of those cuts is numeric

Then I just have to find a way to reduce the field_to_be_processed_further by just those 9 digits in case I have found something looking like the order_nr.

That is another issue, however, and issues are there to be solved ...

Thanks a lot!

Best regards,

DataNibbler

Re: Find a string of a specific format within a field

anbu1984 — Wed, 22 Oct 2014 14:19:56 GMT

Load String,Mid(String,Index(NewStr,'S00000000'),9);

Load *,Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(String,'1','0'),'2','0'),'3','0'),'4','0'),'5','0'),'6','0'),'7','0'),'8','0'),'9','0') As NewStr

Inline [

String

'SN 8638294 / 4 Stück / S80140915']

Re: Find a string of a specific format within a field

datanibbler — Thu, 23 Oct 2014 08:06:36 GMT

Hi tresesco,

I have now found a way: Like I said, what I do is

- I find out how many times I have the letter "S" in that field

- I find out the positions of that letter

- From each of those positions in turn, I cut out a nine-digit-segment

- I check whether there are 8 numbers in that segment

=> In that case, there is an order_number in the field.

The next logical step would be to cut that order_number from that field so that only the remainder

of the field is moved on to further processing.

=> Can that be done? (I know the exact starting_position and length of the segment to cut out)

Thanks a lot!

Best regards,

DataNibbler

Re: Find a string of a specific format within a field

tresB — Thu, 23 Oct 2014 08:12:42 GMT

May be like:

Left(Category,n-1)&Mid(Category,n+len)

n=starting_position

len=length of the segment to be cut off

Re: Find a string of a specific format within a field

datanibbler — Thu, 23 Oct 2014 08:18:24 GMT

That is a good one.

I will try that.