return value based on value between 2 other values

[PeterHenskens]

hi,

I’m pretty new in Qlikview, but wonder if there is a way to return a value if a certain value is between 2 other values

example based on zip codes in US:

let’s say I have a zipcode 651, the value that should return would be 9 (651 is between 600 and 999)

Lower Postal	Upper Postal	Zone
500	599	2
600	999	9
1000	2999	3

I have a table named TX_data

there is a field named [Pickup Postal Code] (search criteria)

in this table I’d like to add a column with "Zone"

table to search in =

Landmark_Zones:
LOAD [Lower Postal], 
[Upper Postal], 
Zone

its also the first time I've posted a question so if things are not propper explaned please ask for more info.

thanks in advance!

[charlie_firemind]

Hi Peter,

Please take a look at how IntervalMatch - Matching intervals to discrete data can help you.

In your example

TX_data:
LOAD * INLINE [
Pickup Postal Code
599
500
750
1500
];

Landmark_Zones:
LOAD * INLINE [
Lower Postal, Upper Postal, Zone
500, 599, 2
600, 999, 9
1000, 2999, 3
];

INNER JOIN IntervalMatch ([Pickup Postal Code])
 
LOAD 
[Lower Postal], 
[Upper Postal]
RESIDENT 
Landmark_Zones;

Also attached in case you prefer to pull apart.

All the best.

C

[PeterHenskens]

Hi Charlie,

many thanks for your responce, but i cannot open your files.

just to be clear TX_Data is +1m rows, looking at your code i should paste all the Pickup postal codes under load * INLINE?

Lower and upper code is 400 rows

[charlie_firemind]

Apologies I just noticed this is View not Sense, thats my fault (The code is exactly the same as attachment though).

That's correct - just add the remaining postal codes you have and you will be ready to go.

If you have the post codes in an excel file or table just switch that with the inline table, that was just to give an example.

Let me know if you have no joy.

[PeterHenskens]

unfortunatly not realy working like I was hoping.

my code:

TX_Data:
LOAD Id,
[Project Code],
[Project Name],
Performance,
Packages,
[SLA Pickup Date],
[Actual Pickup Date],
[Actual Pickup Time],
[Pickup Name],
[Pickup Postal Code],

....

Zones:
LOAD [Lower Postal],
[Upper Postal],
Zone

....

INNER JOIN IntervalMatch ([Delivery Postal Code])
LOAD
[Lower Postal],
[Upper Postal]
RESIDENT
Zones;

result:

Zone should be a number between 1 and 43

[charlie_firemind]

So intervalmatch works with numeric values - The [Delivery Postal Code] above is not numeric as per your original 500 - 599 etc.

Can you please share an example of your Lower Postal], [Upper Postal] table and I will suggest the best route.

Thanks

[PeterHenskens]

hi,

apologies for the delay, attached the postalcode file.

I used to find the match in excel with the lookup formula, tried it in QV but looks like the syntacts is not the same..?

hope you can work this out 🙂