Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create a not in query of a resident table. In SQL the statement would be something like:
SELECT * FROM (SELECT * from table_1 where x = 'y' and z = 'x') t
WHERE id NOT IN (SELECT id from table_1 as t2 where t2.x = 'yy' and z = 'xx')
Can anyone help me?
Thanks,
James Ford
So I guess it's not possible to do this in qlikview? The equivalent SQL-query is so basic. I don't get this?
I think you can use AND mode of listboxes.
And a secred 'red' color of selection that means 'not in'.
Hi,
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.
Data:
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
AutoGenerate 1;
LOAD Distinct
Product as ProductX,
ID as IDX,
chr(39) & ID & chr(39) as lkpIDX
Resident Data
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 ...
=If(IsNull(GetFieldSelections(ProductX)),'0',Concat(distinct lkpIDX,',',IDX))
... 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 ...
=if(Match(ID,$(variable)),Null(),ID)
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
flipside
Thank you.