Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

between texts function

Hi Experts,

I have a scenario where I use a query in a SQL database using "between" function:

  where Field1 between Field2 and Field3

In Qlik script, intervalmatch works fine for numeric fields, but in my case I need to match between two strings.

Had Anyone faced it before ?

Rodolfo Souza

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

There may be an easy solution to this. Check your intervals. If they work like account ranges (which are numerical by default) you could expand all border values to the full 7 digits and use a purely numerical INTERVALMATCH again.

For example, your Intervals table could become something like:

Intervals:

BEGIN   END

4100000 4105001

5700000 5703007

This could be accomplished during the load from your external data source.

View solution in original post

5 Replies
vishsaggi
Champion III
Champion III

Can you post an example with expected output? We might be able to.

Anonymous
Not applicable
Author

Thanks for replying, it would be an example:

Intervals:

BEGIN   END

41          4105001

57          5703007

Table (to be matched):

FIELD

4102004

5701005

when I execute the query at the database:

select * from Intervals inner join Table

on (FIELD between BEGIN and END)

I got this result:

FIELD         BEGIN  END

4102004     41          4105001

5701005     57          5703007

This happens because they are text fields, and thats correct and expected by the requirement.

Previsously  I got access to the database using ODBC, but now I got the individual tables downloaded into my envirorment and I'd like to do this using the script in Qlik.

Does anybody know a way do that?

Rodolfo Souza

vishsaggi
Champion III
Champion III

Use interval Match in Qlikview. Read here:

Interval Match or Inline load

And ref help:

IntervalMatch ‒ QlikView

Peter_Cammaert
Partner - Champion III
Partner - Champion III

There may be an easy solution to this. Check your intervals. If they work like account ranges (which are numerical by default) you could expand all border values to the full 7 digits and use a purely numerical INTERVALMATCH again.

For example, your Intervals table could become something like:

Intervals:

BEGIN   END

4100000 4105001

5700000 5703007

This could be accomplished during the load from your external data source.

Anonymous
Not applicable
Author

Hi Peter,

this solution will work, maybe the lengh would vary but that I can easily control in the script.

Thanks a lot.

Rodolfo Souza