Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
Very detailed. Thanks very much. That's how it worked. 😄