I am looking for a way to exclude my selections rather than including them. Is there a way to do this? I know it is possible to do this with Set Analysis, but this is not what I am looking for.
What I am looking to do, is to create table box, customer list. I want the list to include all customers in group selection one and exclude customers in group selection two. I have not yet found a way to make an exclusion.
Table description: Table A contains customer data Table B contains Campaigns (1,2,3 and 4) Table C contains Campaign 5 (alone)
All tables are linked through customer ID
The customers I want to include are part of Campaign 5. The customers I want to exclude are people who are part of Campaign 1, 2, 3 and 4.
In general I want to make a selection of all customers that are in Campaign 5 and exclude customers in Campaign 1, 2, 3 and 4 from this selection.
I am sorry, but I am not yet skilled or experienced enough with QlikView to be able to understand how and where I am supposed to implement what you just suggested.
I was hoping for something a bit simpler, I would have thought it was. Excluding should not be a programing issue in my opinion at least. The manual also has something written about the issue (QlikView 10 Reference Manual) on page 138.
"Forced Exclusion / Not Selection Closely related to the and selection is the forced exclusion, or not selection. Here it is possible to explicitly exclude a field value, i.e. the solutions found by QlikView may not be associated with the excluded value. The not selection is made by clicking a cell and keeping the mouse button depressed until the cell turns red. CTRL-clicking in this manner is equivalent to an exclusion that is an additional requirement to the previous selections/ exclusions. The not selection can only be made on a field that is in and mode."
This does not seam to work at all any where. The same text can also be found in the QlikView 9 Reference Manual.
I'll try to get back to this in a bit to try to answer your actual question, but attached is an example of and mode, forced exclusion, and how forced exclusion differs from regular exclusion. In this case, I'm demonstrating forced vs. regular exclusion using set analysis. However, you can also demonstrate forced exclusion directly on the list box. Hold down the CTRL key, click and hold on value D. After a few seconds, it will turn red, indicating that you've forced its exclusion. Normal list boxes and normal data sets do not work this way. You have to very specifically set up your data to even allow this, and then you must select "and mode" on the general tab of the list box. I don't know yet if this will be relevant to your original question. Just trying to give you a working example of and mode and forced exclusion to play with.
Can you please post some sample fields and data for your three tables? I'm having a difficult time visualizing your data structures. Do you have a "Campaign" field? If so, wouldn't tables B and C link by Customer AND Campaign? What sort of data is stored in the campaign tables? Is this data different for Campaigns 1-4 and Campaign 5? Is that why you have separate tables, and if not, why DO you have separate tables?
Thank you for taking the time to look in to this issue John.
I have been looking at your sample file and I see the difference. The and mode would probably work well on a list where I only want to exclude from.
To describe my data a bit more detailed, I have the following tables and fields.
Table A: Customer ID Name Address Product A Product B
Table B: Customer ID Previous Campaign name
Table C: Customer ID New Campaign name
I split previous and new campaigns in to 2 different tables assuming it would be easier to exclude previous campaigns from the new campaign that way. Both Table B and C are exported from Excel using CSV format (not that it matters much). All 3 tables are linked through Customer ID, and if I were to select all my New Campaigns and my Previous Campaigns I would of course see all customers that would be included in all campaigns all together.
Ah, OK, I think I see what you're up to now. By selecting the current campaign, but forcing the exclusion of previous campaigns, you would see which customers were lured in by the current campaign, or something like that. And you could do something similar with other campaigns, and maybe not exclude all previous campaigns, but maybe specific ones. The ability to do all that with selections would be nice.
I'm thinking I would have a campaign table with a Campaign ID and Campaign Name, and a Customer vs. Campaign table to tie the two together. The campaign table would also tell you which campaign was the current campaign, perhaps with a flag. Now let's see if I can turn that into an "and mode" list box.
Well, it's working like I think you want it working. I don't guarantee that I've understood the problem, of course. I'm also not sure this is the best solution. Seems like there should be a way to do it with two list boxes instead...
Your example was very helpful and I was able to copy the same function in to my own sheet. However I am not sure it is only like this for me, but it is not possible for me to exclude all campaigns at once. One selection always seam to have to be open, and I am still not ale to fully do what I was looking for.
To explain my goal with this project a bit further. What I am trying to do is to not market to customers who have already been targeted by recent campaigns, to spare them from constantly getting commercial material from us. But there is also a second option for this. We have different products (obviously) and they come from different sets of tables. For different reasons, in some cases excluding seams to be the only way to achieve the result I am looking for.
I am still fairly new to QlikView and can not really say if I am right or wrong about any of this. But I am a bit surprised that you can mainly only include in QlikView and not select what you want to exclude as secondary option.
About the programing in QlikView, could you recommend any books or papers on this?
I believe that it isn't letting us exclude the last campaign because then there would be NO matching data. In my sample, EVERY customer is connected to at least one campaign. Let me remove all campaigns from Werner... nope, it's still not letting me exclude that final campaign. And if I move Werner to campaign 4, then I can exclude campaign 5, but then I can't exclude campaign 4.
One workaround might be a dummy campaign, like campaign 0? Nope. Fail.
Well, maybe it will make more sense to me tomorrow. I'm kind of wiped out today.
As far as a more normal option using QlikView's normal logic and normal list boxes, just create list boxes for the campaigns and customers. Select campaigns 1-4. Right click on the customer list box and click "select excluded". Now select campaign 5. You now have the list of customers in campaign 5 but not in campaigns 1-4. I was just trying to get something that didn't require that many steps. I wanted a simple "these but not these". Probably still a way, and the "and mode" list box wasn't a particularly clear implementation of "these but not these" even if it had worked.
In the mean time, you might try fiddling around with "select excluded" and see if you can do the things you want to do with it.
I can't recommend any books or papers. I don't know if there are any. I took the tutorial, then had a several day class by QlikTech, then years of using it, then a long time spent on the forum.
