search from a range

[#1]

Hi

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.

[sunny_talwar]

May be this:

Table1:

LOAD Circle,

    Low,

    High,

    PF

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;

[kaushiknsolanki]

Hi,

Use the Intervalmatch Function.

IntervalMatch

Regards,

Kaushik Solanki

[#4]

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

[sunny_talwar]

You can use this in that case:

Table1:

LOAD Circle,

    Low,

    High,

    PF

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;