Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two tables.
QVT_Object: Zip code from objects
Zipcode | Sales |
01568 | 1.000 |
58091 | 58.000 |
59889 | 3.000 |
21769 | 14.000 |
VC_VertGebietDef: ZIP Code Areas
Zipcode_min | Zipcode_max |
01000 | 03999 |
56000 | 58999 |
59000 | 59000 |
I would like to assign the zip code from the "QVT_Object" to the zip code areas from the "VC_VertGebietDef".
I've already tried it like this:
if(NUM([QVT_Object.zipcode]) >= NUM(VC_VertGebietDef.Zipcode_min) and NUM([QVT_Object.zipcode]) <= NUM(VC_VertGebietDef.Zipcode_max), VC_VertGebietDef.Zipcode_min & ' - ' & VC_VertGebietDef.Zipcode_max, null())
Unfortunately it doesn't work. Do any of you know?
The result should look like this:
Zip Code Area | Sales |
01000 - 03999 | 1.000 |
56000 - 58999 | 58.000 |
59000 - 59000 | 3.000 |
This sounds like a job for IntervalMatch()
Sales:
Load * INLINE [
Zipcode, Sales
01568, 1000
58091, 58000
59889, 3000
21769, 14000];
Zip:
Load *, Zipcode_min & '-' & Zipcode_max as Zip INLINE [
Zipcode_min, Zipcode_max
01000, 03999
56000, 58999
59000, 59000];
Left join IntervalMatch(Zipcode)
Load Zipcode_min, Zipcode_max
Resident Zip;
Note that your expected result doesn't match your actual values - 59889 is not in the range 59000 - 59000.
This sounds like a job for IntervalMatch()
Sales:
Load * INLINE [
Zipcode, Sales
01568, 1000
58091, 58000
59889, 3000
21769, 14000];
Zip:
Load *, Zipcode_min & '-' & Zipcode_max as Zip INLINE [
Zipcode_min, Zipcode_max
01000, 03999
56000, 58999
59000, 59000];
Left join IntervalMatch(Zipcode)
Load Zipcode_min, Zipcode_max
Resident Zip;
Note that your expected result doesn't match your actual values - 59889 is not in the range 59000 - 59000.
Thank you for your support.
It works exactly how I need it 👍
You're right, I made a mistake in the example 😁