Lookup between two tables

[MK_QSL]

I have two tables and wants below result.

Type	Customer	Sales	Points
DEFEND	Cust1	3093.52	250
DEFEND	Cust2	2725.05	200
ATTACK	Cust3	2988.10	100
ATTACK	Cust4	2620.85	100
ATTACK	Cust5	3387.83	150
ATTACK	Cust6	3864.72	150
DEFEND	Cust7	4314.67	300
DEFEND	Cust8	5703.84	350

[Clever_Anjos]

Best way is reload renaming Type to CriteriaType, so you have a key

[christian77]

Hi.

I've seen
your file. It's sort of complicated. It's impossible to make a join between sales and points since they are by intervals and they also belong to different Type.

So definitely you need to hit the help page and look for INTERVAL MATCH.

That's your solution.

[christian77]

I just did that and it does not work.

[swuehl]

You can use an intervalmatch in your load or advanced aggregation in your chart expression (assuming points are increasing for increasing sales).

For the chart expression, see attached.

[MK_QSL]

How can we solve this case by interval match? if you can provide help....

[christian77]

Great! But only in this case cause each customer has only a type. Criteria is different by Type. Since there are only 2 criterias, you can doble that expresion and you'll have it.

Criteria is increasing but it is not proportional.

[christian77]

Open help menu and type interval match. This funcktion was intended for interval times. For example you can have a product with different discounts or prices along times.

The question is, what price to apply? Depends on time. What criteria to use. In this case depends on Type and on Sales. Take sales as your interval. Repeat it for each type or do compose one.

Syntax and its explanation are complicated also. That’s why is better if you
look at the help menu and see it by yourself.

[swuehl · Accepted solution]

Here is an example how to do this using intervalmatch.

You'll need to prepare your Criteria table that it contains intervals for your points (start sales value, end sales value), you can do this either manually or doing some logic in the script (there are some sample files here in the forum that cope with that).

Then use the extended version of IntervalMatch Load prefix.

[MK_QSL]

Thank you very much.. You make my day....!!