Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rodolfoag
New Contributor III

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

Re: between texts function

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
Esteemed Contributor III

Re: between texts function

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

rodolfoag
New Contributor III

Re: between texts function

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
Esteemed Contributor III

Re: between texts function

Use interval Match in Qlikview. Read here:

Interval Match or Inline load

And ref help:

IntervalMatch ‒ QlikView

Re: between texts function

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

rodolfoag
New Contributor III

Re: between texts function

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