topic Re: if between function in QlikView

if between function

samuel_brierley — Mon, 16 Nov 2015 10:49:17 GMT

Hi all,

i have the following expression but want to change is slightly so it incorporates some kind of between funciton

=IF(Latitude > Lat1 AND Latitude < Lat2 AND Longitude > Long1 AND Longitude < Long2, Area,'')

thanks all

Re: if between function

jonathandienst — Mon, 16 Nov 2015 10:55:24 GMT

There is no between function or operator in Qlikview. In some cases, you can use interval matching to set a flag in the load script, but I don't know how to do that with two matches.

Re: if between function

samuel_brierley — Mon, 16 Nov 2015 10:58:53 GMT

thought that was the case, not a huge issue i can write a lengthy if statement to capture all the iterations, not really that many.

Re: if between function

engishfaque — Mon, 16 Nov 2015 11:17:57 GMT

Dear Samuel,

Please try listed below script,

Load Area

From YourDataSource

Where Latitude > Lat1

And Latitude < Lat2

And Longitude > Long1

And Longitude < Long2;

Please note, Here in QlikView we use IntervalMatch function instead of Between function.

Kind regards,

Ishfaque Ahmed

Re: if between function

samuel_brierley — Mon, 16 Nov 2015 11:43:50 GMT

Hi thanks,

unfortunately this is what i tried earlier and because I have 2 different sources i cant get that method to work. what im trying to do is below, id appreciate if you could help solve my problem.

Table1

,,,,

Table2

[1],[2][3]

Table 3

[1]

lookup( ,

where 2 > and 2 < and [3] > and [3] < ,

Table1)

Re: if between function

Kushal_Chawda — Mon, 16 Nov 2015 12:04:05 GMT

Create the Flag in Script

IF((Latitude > Lat1 AND Latitude < Lat2) AND (Longitude > Long1 AND Longitude < Long2), 1,0) as Flag_Lat_Long

Now you can use this Flag in Where Clause or in Chart using set analysis

Re: if between function

Kushal_Chawda — Mon, 16 Nov 2015 12:05:23 GMT

Is there any link between Table 1 & table 2?

Re: if between function

Anonymous — Mon, 16 Nov 2015 12:07:19 GMT

try this:

=IF((Latitude > Lat1 AND Latitude < Lat2) AND (Longitude > Long1 AND Longitude < Long2), Area,'')

make sure all are in number

Re: if between function

samuel_brierley — Mon, 16 Nov 2015 12:16:35 GMT

Thanks for the help Kush,

no there isnt a link between the two tables and im trying everything i can think of to get this to work.

i cant get my head around the problem. I know you guiys are far more intelligent than i am so obviously im not explaingin very well.

here we go

[MIDAS]

LOAD [Geographic Address],

Latitude,

Longitude

FROM

(qvd);

[AQMA]

LOAD Area,

Lat1,

Lat2,

Long1,

Long2

FROM

(ooxml, embedded labels, table is Sheet1);

i want the AQMA table to join onto the MIDAS table, as you can see no link, what i want is the =IF(Latitude > Lat1 AND Latitude < Lat2 AND Longitude > Long1 AND Longitude < Long2, Area,'') function to be the criteria for what value is put where.

your help is very much appreciated.

Re: if between function

swuehl — Mon, 16 Nov 2015 12:35:52 GMT

What about the solution I've suggested to your discussion here:

Lookup with Multiple Criteria

Re: if between function

Kushal_Chawda — Mon, 16 Nov 2015 12:38:54 GMT

if you can share some sample data then I don't think so It is not possible because people here are very intelligent

Re: if between function

samuel_brierley — Mon, 16 Nov 2015 13:08:55 GMT

please find the attached data,

you will notice in the data that the AQMA table is actually called Sheet1.

thanks for all the help and if you do manage it can you please post the script as im only a personal user.

thanks