How can I assign postcode areas?

[reporting_neu]

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

[Or · Accepted solution]

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.

[reporting_neu]

Thank you for your support.

It works exactly how I need it 👍

You're right, I made a mistake in the example 😁