topic Select Excluded - How to Exclude Records Associated With Table Selection in QlikView

Select Excluded - How to Exclude Records Associated With Table Selection

Wed, 08 Dec 2010 21:04:43 GMT

I have a Many to One relationship between Charges and Visits. In my example below If I choose a 'CptCode' it matches to a 'VisitID' that matches the corresponding 'Code'.

What I would like to do is also select a 'CPTCode' and identify all visits that do not match. If I use 'Select Excluded' it will still pull some visits I don't want because it will only exclude visits where the excluded CPTCode is the only one on the visit.

I thought about a workaround by modifying the SQL but I don't think it should be necessary. What is the best approach?

LOAD Code,
CptCode;
SQL SELECT Code,
CptCode
FROM WINDOPATH.dbo.WorkloadDefinitionTable;

ChargeSlipTable:
LOAD Code,
Visit As VisitID;
SQL SELECT Code,
SuperobjectID,
Visit
FROM WINDOPATH.dbo.ChargeSlipTable;

Select Excluded - How to Exclude Records Associated With Table Selection

Wed, 08 Dec 2010 21:57:05 GMT

I am a bit confused to why Select Excluded didnt work. Anyhow what about Select Excluded on VisitID it might fix the problem.

If you just need the to show a resulting calculation in a graph you could use set analysis.

Another way is to make a "flag" per distinct value in CptCode which is true/flase depending on if the VisistID/Code (lowest level) is in the CptCode . IE "Flag_Visited_CptCodeNumber". Its a bit tricky, but first you can load a table with distinct CptCode values. Then you use a for-loop that goes goes through the list creats a variable which consists of if-statments you later call in your load. I made one a few weeks back. Could be made easier to follow but hopefully it gives a general idea of this approch.


negative_filter_page:
LOAD * INLINE [
 Page
 xxx
 yyy
];

if noofrows('negative_filter_page')>0 then
 let samesession='peek(#Visitor#)=Visitor and peek(#Time#)=Time and peek(#End time#)=[End time]';
 let counter='0';
 let peekpage=peek('Page','$(counter)','negative_filter_page');
 let name_endstring='_flag_temp';
 let name_desc_endstring='_flag';
 let string=',if('&'$(samesession)'&',if(peek(#'&'$(peekpage)'&'$(name_endstring)'&'#)=#Visited#,#Visited#,if(#'&'$(peekpage)'&'#=Page,#Visited#,#Not Visited#)),if(#'&'$(peekpage)'&'#=Page,#Visited#,#Not Visited#)) as [' & '$(peekpage)'&'$(name_endstring)'&']';
 let desc_string=',if('&'$(samesession)'&',if(peek(#' & '$(peekpage)'&'$(name_desc_endstring)'&'#)=#Visited#,#Visited#,#Not Visited#),if(['&'$(peekpage)'&'$(name_endstring)'&']=#Visited#,#Visited#,#Not Visited#)) as [' & '$(peekpage)'&'$(name_desc_endstring)'&']';
 for counter=1 to noofrows('negative_filter_page')-1
 let peekpage=peek('Page','$(counter)','negative_filter_page');
 let string='$(string)'&',if('&'$(samesession)'&',if(peek(#'&'$(peekpage)'&'$(name_endstring)'&'#)=#Visited#,#Visited#,if(#'&'$(peekpage)'&'#=Page,#Visited#,#Not Visited#)),if(#'&'$(peekpage)'&'#=Page,#Visited#,#Not Visited#)) as [' & '$(peekpage)'&'$(name_endstring)'&']';
 let desc_string='$(desc_string)'&',if('&'$(samesession)'&',if(peek(#' & '$(peekpage)'&'$(name_desc_endstring)'&'#)=#Visited#,#Visited#,#Not Visited#),if(['&'$(peekpage)'&'$(name_endstring)'&']=#Visited#,#Visited#,#Not Visited#)) as [' & '$(peekpage)'&'$(name_desc_endstring)'&']';
 next
 let negative_filter_strings = replace('$(string)','#',chr(39));
 let negative_filter_desc_strings = replace('$(desc_string)','#',chr(39));
else
 let negative_filter_strings='';
end if

load
..................
$(negative_filter_strings)
resident ...;

Select Excluded - How to Exclude Records Associated With Table Selection

Thu, 09 Dec 2010 16:02:30 GMT

As I understand it the Select Excluded doesn't work because of the many to one relationship.

So for example if select CptCode 'A' it will match to VisitID 55 and 27. But if I Select Exclude 'A' it will match to 55 and 38 because the 'C' that matches to 55 is not excluded. What I want is in the example is to only return 38.

CptCode | Code | VisitID

A | 1 | 55

C | 3 | 55

A | 1 | 27

B | 2 | 38

D | 4 | 38

E | 5 | 38

Your flag option seems plausible but it appears a bit too complicated.

Select Excluded - How to Exclude Records Associated With Table Selection

Thu, 09 Dec 2010 20:29:12 GMT

So I am now trying to limit which CptCodes are allowed by adding a WHERE Clause on the ChargeSlipTable table. Obviously this doesn't get what I want because all case visits are returned regardless. Am I able to load a table that is a inner join between two tables? Can I add parameters to an existing join that QlikView creates?

ChargeSlipTable:
LOAD Code,
Visit As VisitID;
SQL SELECT Code,
SuperobjectID,
Visit
FROM WINDOPATH.dbo.ChargeSlipTable
WHERE Code NOT IN ('4020830','4020831','4020859','4020856');

CaseTable:
LOAD Number As Case,
Visit As VisitID;
SQL SELECT Number,
Visit
FROM WINDOPATH.dbo.CaseTable;

Select Excluded - How to Exclude Records Associated With Table Selection

Thu, 09 Dec 2010 22:57:53 GMT

" So for example if select CptCode 'A' it will match to VisitID 55 and 27. But if I Select Exclude 'A' it will match to 55 and 38 because the 'C' that matches to 55 is not excluded. What I want is in the example is to only return 38."

If you start with selecting CPtCode value 'A' and then chose 'select excluded' in a VisitID field (a listbox does the work) you should get what you are looking for.