Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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
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 🙂