script question about selecting a region

[heij1421]

Hi,

I have two tables

Region:
LOAD * INLINE
"zipcode_from, zipcode_to, region
1000, 2000, North
2000, 3000, West
3000, 4000, East";

Customers:
LOAD * INLINE
"CustomerId, zipcode
1, 1100
2, 2000,
3, 2234
"

Now I want to know in which regions my customers are, based on the zipcode of the customer

Customer 1 has zipcode 1100 and this code is between 1000 and 2000, so this customer is situated in region North.

How can I add the region to the customer table?

[rubenmarin · Accepted solution]

Hi, you can use intervalmatch to assign a value between a range, check the example on the help page: https://help.qlik.com/es-ES/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

[heij1421]

Thanks Rubenmarin, that helps:

Customerstemp:
LOAD * INLINE
"CustomerId, zipcode
1, 1100
2, 2000,
3, 2234
";

Regiontemp:
LOAD * INLINE
"zipcode_from, zipcode_to, region
1000, 2000, North
2001, 3000, West
3001, 4000, East";

Inner Join IntervalMatch ( zipcode )
LOAD zipcode_from, zipcode_to
Resident Regiontemp;

[rubenmarin]

And you can merge region to the customer table if you add

Left join (Customerstemp)
LOAD zipcode, region Resident Regiontemp;

DROP Table Regiontemp;