Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
PeterHenskens
Contributor II
Contributor II

return value based on value between 2 other values

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!

 

6 Replies
charlie_firemind
Partner - Contributor III
Partner - Contributor III

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
Contributor II
Contributor II
Author

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
Partner - Contributor III
Partner - Contributor III

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
Contributor II
Contributor II
Author

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 43Zone should be a number between 1 and 43

charlie_firemind
Partner - Contributor III
Partner - Contributor III

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
Contributor II
Contributor II
Author

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 🙂