Have a look at the attached appication (courtsey: some community user), might help.
notExists.qvw 124.2 K
Hello again, let me show you what I want to achieve.
Lets say we have the following table:
First I would like to select the customers that is active on Product A:
SELECT * FROM Customers WHERE Product = 'A' and Active = 'Y'
This will give us customers with id 1 and 3.
And based on that selection I would like to exclude those that are NOT active on product B. This would give us customer 3.
So there is only one table involed. Based on the first selection I would like to create a NOT IN query on the same table.
Thanks for the answer. But that is not what I want to achieve.
I want to use two listboxes to make my selection. The first one would be the 'Active On Product' selection. And the second box would be based on the first selection of the 'Active On Product' listbox. And would be 'Not active on' where you select one or more products that the customers are NOT active on based on the first selection.
Try to use this where statement:
WHERE not wildmatch(FieldName, 'value', 'value2', 'value3')
value, value2, value3 should be data you want to exclude.
You can either use wildmatch or mixmatch function.
wildmatch --> 'valu*' (wildmatch search)
mixmatch --> 'value' (exact data value search)
Hope it helps.
notWildmatch.qvw 146.0 K
I've been stumped by this simple problem but think I may have a solution. Due to Qlikview's associative logic, you would need to first create a disassociated table to enable selection of product codes excluded by the first selection.
Load * Inline [
ID, Product, Active
1, A, Y
1, B, N
2, C, Y
3, A, Y
3, B, Y
3, B, N
4, C, N
10, A, Y
3, A, N];
Inactives: //Creates a data island to allow selection of same products
LOAD 'No exclusions' as ProductX,
0 as IDX,
chr(39) & 0 & chr(39) as lkpIDX
Product as ProductX,
ID as IDX,
chr(39) & ID & chr(39) as lkpIDX
Where Active = 'N';
In this table I included fields wrapped with the ' char to simplify a concat statement in a variable. You then need to set up a variable with the following statement ...
... which creates a string to match to if there are selections in the excluded products.
After you have two list boxes - one for Product and one for ProductX (excluded) - your final listbox or chart needs this expression ...
This is easier in a SQL front-end (such as SSRS) because you re-submit the query with the required parameters to return the data, whereas in Qlikview your data is all there and you are filtering it down.
Hope this helps