between texts function

[Anonymous]

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

[vishsaggi]

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

[Anonymous]

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]

Use interval Match in Qlikview. Read here:

Interval Match or Inline load

And ref help:

IntervalMatch ‒ QlikView

[Peter_Cammaert · Accepted solution]

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]

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