Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis - Only if match between keys?

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 ATable B
VendorVendor
Vendor AVendor C
Vendor BVendor D
Vendor CVendor E
Vendor D
Vendor E

Appreciate your input!

1 Solution

Accepted Solutions
Not applicable

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

AJ

View solution in original post

5 Replies
Not applicable

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

AJ

View solution in original post

trey_bayne
Partner
Partner

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.

its_anandrjs

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

sujeetsingh
Master III
Master III

Use limiting expressions while LOAD

Not applicable

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.