Between function

[#1]

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

[oknotsen · Accepted solution]

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;

[#3]

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!

[#4]

Thanks! This works!

[oknotsen]

Happy I could help .