topic Re: Lookup between two tables in QlikView

Lookup between two tables

MK_QSL — Thu, 07 Nov 2013 14:57:08 GMT

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

Re: Lookup between two tables

Clever_Anjos — Thu, 07 Nov 2013 15:03:11 GMT

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

Re: Lookup between two tables

christian77 — Thu, 07 Nov 2013 15:10:53 GMT

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.

Re: Re: Lookup between two tables

christian77 — Thu, 07 Nov 2013 15:12:25 GMT

I just did that and it does not work.

Re: Lookup between two tables

swuehl — Thu, 07 Nov 2013 15:18:20 GMT

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.

Re: Lookup between two tables

MK_QSL — Thu, 07 Nov 2013 15:27:04 GMT

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

Re: Re: Lookup between two tables

christian77 — Thu, 07 Nov 2013 15:28:25 GMT

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.

Re: Re: Lookup between two tables

christian77 — Thu, 07 Nov 2013 15:38:06 GMT

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.

Re: Re: Lookup between two tables

swuehl — Thu, 07 Nov 2013 15:54:46 GMT

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.

Re: Lookup between two tables

MK_QSL — Thu, 07 Nov 2013 15:57:56 GMT

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