topic Re: return value based on value between 2 other values in QlikView

return value based on value between 2 other values

PeterHenskens — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: return value based on value between 2 other values

charlie_firemind — Sat, 17 Nov 2018 13:14:13 GMT

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.

Re: return value based on value between 2 other values

PeterHenskens — Sat, 17 Nov 2018 14:41:24 GMT

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

Re: return value based on value between 2 other values

charlie_firemind — Sat, 17 Nov 2018 14:46:33 GMT

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.

Re: return value based on value between 2 other values

PeterHenskens — Sat, 17 Nov 2018 16:09:35 GMT

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

Re: return value based on value between 2 other values

charlie_firemind — Sat, 17 Nov 2018 19:03:57 GMT

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

Re: return value based on value between 2 other values

PeterHenskens — Sun, 18 Nov 2018 17:33:37 GMT

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 🙂