topic Matching if exist or not in QlikView

Matching if exist or not

Tue, 17 Mar 2015 13:57:07 GMT

Hi Dear All,

Basically, I have 2 fields in table1 which are: and 3 fields in Table2 which are: What I want is:

Whether the distributor and ProductName both of them at the same time are available in table2 . If they together exist then I should be able to write "1" to somewhere (maybe in a new field in table2), otherwise if they dont exist in table2 I should be able to write "0" to somewhere to able to promote these distributors. Do you have any clear idea about how to do that?

Thank you in advance..

Table1:

Distributor	ProductName
12001	A
12002	B
12003	C
12004	D
12005	E
12002	F
12003	G

Table2:

Distributor	ProductName	Amount
12001	A	9
12003	B	20
12003	C	16
12003	D	15
12005	E	5
12001	F	4
12003	G	5

Re: Matching if exist or not

avinashelite — Tue, 17 Mar 2015 14:01:23 GMT

try like this:

table1:

load Distributor&'-'&ProductName as table1key,

* from tablename1;

table2:

load if(exist(table1key,Distributor&'-'&ProductName)='-1',1,0) as present/notpresent,

* from

tablename2.

Re: Matching if exist or not

Tue, 17 Mar 2015 15:09:14 GMT

Hi Dear Avinash,

Thank you so much for that prompt helpful reply, very grateful. I works perfect. I just changed slightly the field names in table1 not to have sytnetic relation in tables. Hovewer, I want it make it differanciate when it is "0", the are 2 possiblities, table1key is not there, it doesnt show this . For example: Distributor 12002 and ProductName B is not shown anywhere. What the code give "0" for the one is not in table1 and available in table2 . For instance: 12001 distributor and Name F. The code assign for this "0". I want it to differantiate from the one i explained with assigning for instance -1. Do you know how can i do it? I attached the qlikview.

Thanks alot...

Table1:

LOAD *, DistributorH&'-'&ProductNameH as table1key;

LOAD * INLINE [

DistributorH, ProductNameH

12001, A

12002, B

12003, C

12004, D

12005, E

12002, F

12003, G

];

Table2:

LOAD *,if(exists(table1key,Distributor&'-'&ProductName)='-1',1,0) as VarYok;

LOAD * INLINE [

Distributor, ProductName, Amount

12001, A, 9

12003, B, 20

12003, C, 16

12003, D, 15

12005, E, 5

12001, F, 4

12003, G, 5

];

Kezy