topic Re: search from a range in QlikView

search from a range

Mon, 22 Feb 2016 09:24:12 GMT

I have a lookup table like this. say crosstab:

Circle	Low	High	PF
AP	1	10	.5
AP	11	20	1
AP	21	9999	2
BR	1	30	0
BR	31	9999	1

Another table has following data:

Circle	Product	Count
AP	AAA	40
AP	BBB	101
BR	AAA	10

I need to put PF in table 2, based on looking at table2. count between Low and High value in table A.

Tried IntervalMatch. But as table A has Circle as a parameter, does not seem to work. (Maybe I don't know how to use).

Any inputs to above question will be appreciated.

Re: search from a range

sunny_talwar — Mon, 22 Feb 2016 10:47:16 GMT

May be this:

Table1:

LOAD Circle,

Low,

High,

FROM

[https://community.qlik.com/thread/206452]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD Circle,

Product,

Count

FROM

[https://community.qlik.com/thread/206452]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table1)

IntervalMatch (Count, Circle)

LOAD Low,

High,

Circle

Resident Table1;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

Re: search from a range

kaushiknsolanki — Mon, 22 Feb 2016 10:48:21 GMT

Hi,

Use the Intervalmatch Function.

IntervalMatch

Regards,

Kaushik Solanki

Re: search from a range

Mon, 22 Feb 2016 11:44:09 GMT

Thanks, but With this solution, more rows are coming. there should only be 3 rows

Circle	Product	Count	PF
AP			.5
AP			1
AP	AAA	40	2
AP	BBB	101	2
BR	AAA	10	0
BR			1

Re: search from a range

sunny_talwar — Mon, 22 Feb 2016 13:13:50 GMT

You can use this in that case:

Table1:

LOAD Circle,

Low,

High,

FROM

[https://community.qlik.com/thread/206452]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD Circle,

Product,

Count

FROM

[https://community.qlik.com/thread/206452]

(html, codepage is 1252, embedded labels, table is @2);

Left Join (Table1)

IntervalMatch (Count, Circle)

LOAD Low,

High,

Circle

Resident Table1;

Inner Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;