Making an exclusion list and handling nulls

[ttmaroney]

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]);

[sunny_talwar]

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]);

[ttmaroney]

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

  …

[sunny_talwar]

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

[ttmaroney]

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
…

[vinieme12]

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,

    A,

    b,

    c

FROM

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

WHERE

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

;

[vinieme12 · Accepted solution]

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;

[ttmaroney]

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.