Index query for strings that contain numbers

[ecak2105]

Hi guys,

I have following issue with this statement right here for qlikview:

if (index ([SA-Code],Mid('S100A', 3))>=100 and ([SA-Code],Mid('S199A', 3))<=199, 'SA: '&[SA-Code]

I want to get all Elements between S100A and S199A. How do I transform this string in order to make a query that gives the wanted output?

I'd be delighted to get any help here!

Thanks in advance 🙂 

[justISO]

Hi, maybe it is easier to convert that 'middle' in string to number and check condition it it is between 100 and 200?:

if( Num(KeepChar([SA-CODE], '0123456789'))>=100 and Num(KeepChar([SA-CODE], '0123456789'))<200, 'SA: '&[SA-CODE])

because not sure if qlikview index differs from qliksense, but I believe you are using it wrong.

[ecak2105]

Hi,

I've tried your statement in various ways but it is unfortunately not working..

is there a possibility to do a 'like' statement here?

For example: if index([SA-CODE], like ('S10%A'), 'SA: ' &[SA-CODE]) 

Thank your very much for your help

[Or]

WildMatch([SA-CODE],'S10??A') would be the equivalent of the like statement (well, actually, it'd be 'S10*A' as you wrote it but that isn't as accurate). However, that would also catch S10AAA, which isn't in the range you described, so it depends on whether your data can have non-numeric characters in these positions or not.

[ecak2105]

so it would be:

if WildMatch([SA-CODE],'S10*A') , 'SA: ' &[SA-CODE])?

Thank you 🙂 

[Or · Accepted solution]

With that specific syntax, you will catch anything that starts with S10 and ends in A, no matter how many and which characters are between those bookends. Using the ?? syntax would catch only cases where there are exactly two characters between the S10 and the A, so it is more accurate, but if * does the job then by all means...

[ecak2105]

Hi,

it worked exactly how I wanted it thanks to the wildmatch 🙂 

I owe you one! Thank you

for the rest of you interested in how I solved the topic excactly
here is the beginning:



=concat(distinct
if(index
...

and the clause for "like" aka wildmatch statement
IF (WildMatch([SA-Code],'S11*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S12*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S13*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S14*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S15*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S16*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S17*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S18*A'),'SA: '&[SA-Code],
IF (WildMatch([SA-Code],'S19*A'),'SA: '&[SA-Code],

Wish you all a successful rest of the week