Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
reporting_neu
Creator II
Creator II

How can I assign postcode areas?

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
Labels (2)
1 Solution

Accepted Solutions
Or
MVP
MVP

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.

View solution in original post

2 Replies
Or
MVP
MVP

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

Thank you for your support.

It works exactly how I need it 👍

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