topic Between function in QlikView

Between function

Fri, 11 Dec 2015 09:20:51 GMT

Hi,

I'm trying to create a "dynamic" hierarchy based on postal codes. Postal code 06500 belongs to South-area 66400 to West-area etc. I understood, that there is no between-function QlikView? So, I did this using "if", but it does not work properly.

Could the problem be with data value format? Shoud postal codes be converted to numbers? For example postal code 13330 is not getting value East, what it should get.

Here is the script I wrote for this:

		If(PostalCode >= '00000' and PostalCode<= '09999', 'South'
		, If(PostalCode >= '11000' and PostalCode <= '11499', 'East'
		, If(PostalCode >= '65000' and PostalCode <= '69999', 'West'
		, 0))) as Area

Re: Between function

oknotsen — Fri, 11 Dec 2015 10:31:26 GMT

You probably want to do this in script by using the "IntervalMatch" function.

Let's assume you have two tables already loaded:

- A table with PostalCode ranges (field with start of range, end of range and some value

- A table that has the PostalCode to match in it

Now below that, add a table like this:

Inner Join (PostalCodeRangeTable)
IntervalMatch (PostalCode)
Load
BeginPostalCodeRange,
EndPostalCodeRange
Resident PostalCodeRangeTable;

Re: Between function

Fri, 11 Dec 2015 10:45:46 GMT

Yes, I have a table where postal code is (customer information table). And yes, I will creat a table with postal code ranges in it just like you descriped.

I'll try this!

Re: Between function

Mon, 14 Dec 2015 08:02:57 GMT

Thanks! This works!

Re: Between function

oknotsen — Mon, 14 Dec 2015 09:45:12 GMT

Happy I could help .