How to include postcodes "from" 5X "to" 6X in the map

[reporting_neu]

Hello community,

I have a table of sales areas with a "Max"-field and a "Min"-field. A sales area has several characteristics.

Example:

Sales area	Postcode min	Postcode max	State
AB	44000	44389	DE
AB	44500	44539	DE
AB	57000	57391	DE
BG	97240	97242	DE

In a second table I have the customers with a zip code.

Example:

Customer	Postcode	State
Customer1	44200	DE
Customer2	97241	DE
Customer3	44501	DE

I would like to differentiate the sales areas by color. In addition, the customers and their sales should then be shown on the map.

First: How do I get a connection from the customer to the sales areas if the sales areas have Min- and Max-fields?

Second: How can I separate the sales areas by color? (The sales of the customers should be given an additional color afterwards and cover the sales areas).

Objective: I would like to have an area map where I can see at a glance which postcode areas have not yet been properly processed. So no sales or little sales were generated.

Can you help me there?

[rbartley · Accepted solution]

Hi,

First: How do I get a connection from the customer to the sales areas if the sales areas have Min- and Max-fields?

Use the IntervalMatch() function in your load script

[Customer]:
Load * Inline [
Customer	Postcode
Customer1	44200
Customer2	97241
Customer3	44501]
(delimiter is  '\t');


[Sales Area]:
Load * Inline [
Sales area	Postcode_min	Postcode_max	State
AB	44000	44389	DE
AB	44500	44539	DE
AB	57000	57391	DE
BG	97240	97242	DE]
(delimiter is  '\t');


//Link the field Postcode to the Postcode intervals defined by the fields Postcode_min and Postcode_max.
Inner Join IntervalMatch ( [Postcode] ) 
LOAD Postcode_min, Postcode_max
Resident [Sales Area];


//colours for each sales area.  There are 3 fields representing the red, green and blue values
[Sales Area Colours]:
Load * Inline [
Sales area	r	g	b
AB	255	0	0
BG	128	255	0]
(delimiter is  '\t');

Second: How can I separate the sales areas by color? (The sales of the customers should be given an additional color afterwards and cover the sales areas).

This can be done either by defining the Sales Area field as a measure and using color by dimension in the measure field in the table or by loading colours in the load script as in the example above and then setting the background color expression to: =rgb(r,g,b)

Objective: I would like to have an area map where I can see at a glance which postcode areas have not yet been properly processed. So no sales or little sales were generated.

You'll need to be more specific in your definition of geographical field as it's possible that more than one country will have the same postcode.  It's not clear from your example. However, in theory, you should just be able to specify the state or postcode as a dimension in the map visualization.

[reporting_neu]

Very detailed. Thanks very much. That's how it worked. 😄