3 Replies Latest reply: Mar 14, 2014 1:46 PM by Nicole Smith RSS

    Need Help Regarding Filtering Data


      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

            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

            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;