topic Re: Making an exclusion list and handling nulls in QlikView

Making an exclusion list and handling nulls

ttmaroney — Thu, 07 Jul 2016 17:24:12 GMT

I’m building a QV application to make reports on projects. The data is exported into Excel from a project management program. It contains many projects that were not really projects, and these need to be excluded from the reports. Ideally I want to make a list (preferably in Excel) of the excluded projects- that way the users can add excluded projects without editing my load statement and they can still be examined if needed (which they can’t if they are excluded from the load with a where statement). I am having problems combining the two in a way such that I can get a yes or no for Exclude (my intent being to use a trigger to exclude those with a “Yes” in [Exclude]. I can’t do it now with only yes or null.

The statement below gives me yes or null for [Exclude]. Any suggestions?

Directory;
Projects:
LOAD Project,
Exclude,
if(isnull(Exclude),'No','Yes') as [Excluded Project]
FROM
[Data\All Projects.xlsx]
(ooxml, embedded labels, table is [Excluded Projects]);

Concatenate(Projects)

LOAD Rank,
Project,
Manager,
[Stakeholder Group],
Progress,

…

FROM
[Data\All Projects.xlsx]
(ooxml, embedded labels, table is [Project List1]);

Re: Making an exclusion list and handling nulls

sunny_talwar — Thu, 07 Jul 2016 17:30:31 GMT

Can you try using Len(Trim(Exclude)) > 0 instead?

Projects:
LOAD Project,
Exclude,
if(Len(Trim(Exclude)) = 0,'No','Yes') as [Excluded Project]
FROM
[Data\All Projects.xlsx]
(ooxml, embedded labels, table is [Excluded Projects]);

Re: Making an exclusion list and handling nulls

ttmaroney — Thu, 07 Jul 2016 17:47:13 GMT

Sunny, your suggestion "if(Len(Trim(Exclude)) = 0,'No','Yes') as [Excluded Project] " does the same thing as when I used "if(isnull(Exclude),'No','Yes') as [Excluded Project]. "

They both return "Yes" when the project is on the excluded list and Excluded is “Yes”, but only returns a "No" if it's on the Excluded list and Excluded is Null, like NotProj3 below. I think the problem may be in how the two tables ore combined.

Excluded List:

Project Excluded

NotProj1 Yes

NotProj2 Yes

NotProj3

Project List:

Project Manager Stakeholder Group …

Proj1 Smith, Dave Oncology

Proj2 Jones, Nancy Cardiology

…

Re: Making an exclusion list and handling nulls

sunny_talwar — Thu, 07 Jul 2016 17:56:05 GMT

Yeses and nos are assigned to a new field called Excluded Project, isn't it? Excluded is the field used to create them?

Re: Making an exclusion list and handling nulls

ttmaroney — Thu, 07 Jul 2016 18:07:56 GMT

Right now the Excluded list has the [Project] and “Yes” for [Exclude]. I just made one blank to test the code.

Excluded List:
Project      Excluded
NotProj1    Yes
NotProj2    Yes
NotProj3

What I’m getting in a table using the code above is something like this, with a majority of the [Excluded] as null:

Project       Excluded Project           Manager           Stakeholder Group   …
Proj1                       -                        Smith, Dave     Oncology
Proj2                       -                         Jones, Nancy   Cardiology
NotProj1              Yes
NotProj2             Yes
NotProj3              No
…

Re: Making an exclusion list and handling nulls

vinieme12 — Thu, 07 Jul 2016 18:17:22 GMT

Hi Thomas,

Please see attached QVW.

LOAD Your Exclusion list, and use applymap to filter out unwanted projects

Exclude:

Mapping LOAD Project,

Exclude

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

ALLDATA:

LOAD Project,

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

WHERE

isnull((ApplyMap('Exclude',Project,null())))

;

Re: Making an exclusion list and handling nulls

vinieme12 — Thu, 07 Jul 2016 18:35:39 GMT

Hi Thomas,

If you have a exclude list and are worried about Nulls, then why not pre-load a static Yes for Exclude list and then concatenate on the resident table?

Exclude:

Project,

'Yes' as Excluded,

OtherField

FROM Xxxxxxxxxxxxxxxx;

Re: Making an exclusion list and handling nulls

ttmaroney — Fri, 08 Jul 2016 11:39:35 GMT

Wow, that was exigently simple. I did change the 'Yes' to 'No', in the load statement so it would just default to "No" or not excluding projects unless told otherwise. Thank you both for your quick suggestions. I'm sure the second option would have worked, but given the choice, I'm going with adding one line to a load statement. Thanks again.