topic Re: Set Analysis - Only if match between keys? in QlikView

Set Analysis - Only if match between keys?

Mon, 21 Apr 2014 21:08:55 GMT

Hi Experts,

Interested in your thoughts on how to handle this.

Still feel quite new with Qlikview and I've ran into a bit of a struggle with the following situation.

How do I count the Vendors NOT in Table B (Vendor A and B) with Vendor acting as a Key between the two tables. The result I'm looking for in the example below is 2. Is there anyway to specify exactly what I'd like to be counted count(Table A.Vendor) - count(Table B.Vendor) ?

Table A		Table B
Vendor		Vendor
Vendor A		Vendor C
Vendor B		Vendor D
Vendor C		Vendor E
Vendor D
Vendor E

Appreciate your input!

Re: Set Analysis - Only if match between keys?

Mon, 21 Apr 2014 21:56:51 GMT

Hey Greg,

The way I would do it is -

Table A:

Load *,

Vendor as CountFlag

From Table A;

Left join

Vendor,

1 as Flag

From Table B;

Now on the Front end you can just do Count(Distinct if(isnull(Flag),CountFlag)) provided the Vendor key is unique(Primary Key) in Table A.

Hope it helps

Thanks

Re: Set Analysis - Only if match between keys?

trey_bayne — Tue, 22 Apr 2014 03:05:01 GMT

How about

Table B:

Load *

From Table B;

Table A:

Load *

From Table A

Where Not Exists(Vendor);

Drop Table B;

This eliminates all the vendors in B and only keeps the vendors in A that are needed for counting. This also reduces the size of your data and maintains your optimized load (assuming you are using .qvd's). The downside here is that you are throwing out data. Verify that you don't need it later in the load.

Re: Set Analysis - Only if match between keys?

its_anandrjs — Tue, 22 Apr 2014 03:37:18 GMT

Load your table like

TableB:

load * Inline

[

Vendor

Vendor C

Vendor D

Vendor E

];

TableA:

Load * Inline

[

Vendor

Vendor A

Vendor B

Vendor C

Vendor D

Vendor E

]Where Exists(Vendor,Vendor);

and then count the Vendor on the front end

Re: Set Analysis - Only if match between keys?

sujeetsingh — Tue, 22 Apr 2014 03:51:22 GMT

Use limiting expressions while LOAD

Re: Set Analysis - Only if match between keys?

Wed, 23 Apr 2014 13:55:26 GMT

Thanks everyone for your answers. I wanted to keep the original data in tact so I picked the answer that solved the issue that way. The other ways certainly work and are probably more efficient, but I wasn't looking for efficiency here.