topic Re: between texts function in QlikView

between texts function

Anonymous — Wed, 16 Aug 2017 20:15:58 GMT

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

Re: between texts function

vishsaggi — Wed, 16 Aug 2017 21:08:43 GMT

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

Re: between texts function

Anonymous — Thu, 17 Aug 2017 12:39:34 GMT

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

Re: between texts function

vishsaggi — Thu, 17 Aug 2017 15:38:04 GMT

Use interval Match in Qlikview. Read here:

Interval Match or Inline load

And ref help:

IntervalMatch ‒ QlikView

Re: between texts function

Peter_Cammaert — Thu, 17 Aug 2017 16:12:13 GMT

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.

Re: between texts function

Anonymous — Thu, 17 Aug 2017 16:29:25 GMT

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