Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.
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
Use limiting expressions while LOAD
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.