topic Re: Need Help Regarding Filtering Data in Water Cooler

Need Help Regarding Filtering Data

Thu, 13 Mar 2014 13:54:21 GMT

Hi. I have 2 lists of information. the first list contains information regarding over 100 agencies across 15 lines of Business.

The second list is a list of agencies and their excluded Business lines.

I have combined the 2 lists and am able to view the total data, and the data on the excluded lines, but what I can't do is filter out the excluded Business to just leave the non-excluded Business.

Any thoughts??

I have recreated the issue in a very simplified version in the attached files :-

Re: Need Help Regarding Filtering Data

alexandros17 — Thu, 13 Mar 2014 14:00:49 GMT

Here it is

Re: Need Help Regarding Filtering Data

Thu, 13 Mar 2014 14:40:19 GMT

Alessandro, thanks for taking the time to answer this. I am still using my Personal edition of Qlikview until I am able to demonstrate a rationale for a licence based on finding solutions for things like this, so am unable to open your amended file. are you ableto explain in words to me what steps you have gone through?

Regards,

Chris

Re: Need Help Regarding Filtering Data

Nicole-Smith — Fri, 14 Mar 2014 17:46:24 GMT

It isn't working because QV doesn't allow you to select a null value to filter on, so what you need to do is populate those null values with something. Here is an example of how to do that with your sample data in the load script (if you swap out the load script you have in your sample file, and replace it with this, you should see that the nulls are replaced by "Not Excluded", and you can then click on the value):

Data:

LOAD Agency,

    [Bus Class],

    Premium

FROM Data.xls

(biff, embedded labels);

LEFT JOIN (Data)

LOAD Agency,

    [Bus Class],

    Excluded as ExcludedTemp

FROM Exclusions.xls

(biff, embedded labels);

LEFT JOIN (Data)

LOAD Agency,

    [Bus Class],

    Premium,

    if(ExcludedTemp = 'Excluded', 'Excluded', 'Not Excluded') as Excluded

RESIDENT Data;

DROP FIELD ExcludedTemp;