Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Hi,
Use the Intervalmatch Function.
Regards,
Kaushik Solanki
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 |
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;