You can't nullify machine guns and el Caminos

Someday I want to be badass enough to rescue a damsel in distress by shooting a machine gun from the hip while riding in the bed of an el Camino that's fishtailing through a dirt parking lot.

Until then, I'll have to settle for opening day of G.I. Joe: Retaliation and publishing awesome Qlikview tips. And being really, really, ridiculously funny.

I can't think of a clever way to tease this tip, but it will let you remove a value from a table, but still keep nulls. Here's the scenario:

We're displaying opportunity details in a straight table. One of the fields contains 'reasons for close'. One of those reasons is 'duplicate'. But since we want to show both open and closed opportunities that aren't closed because they're duplicates, we can't easily use set analysis since it won't gracefully handle the nulls.

To make this work, we'll create three variables, a calculated dimension and a button.

Step One: The Variables

As in all things Qlikview, there are different ways to create variables. I've described creating them in the Document Properties before, so this time let's use the Variable Overview. You can open it by either pressing CTRL+ALT+V or using the menu Settings > Variable Overview. Either way, you'll see something similar to:

With this dialog, you can create, remove, define or comment a variable. The first one we are going to create for this project is vShowDuplicateOpps.

Click add and give it the name vShowDuplicateOpps. Click OK. Click on the new variable in the list and enter the following in the Definition box:

     Opportunity.Key

Add another and give it the name vHideDuplicateOpps. Click on the new variable in the list and enter the following in the Definition box:

     if([Opportunity.Lost Reason] <> 'Duplicate Opportunity', [Opportunity.Key] )

Notice that there is no '=' at the beginning of the expression. This is important.

Add another and give it the name vDuplicateOppsToggle. No need to add a definition.

Step Two: The Calculated Dimension

The more observant among you might know that I think IF statements are of the devil. So imagine me poking you with a burning pitchfork during Step Two. What the heck, you should always imagine that.

Add a Calculated Dimension, promote it and label it. Don't forget to 'Suppress When Value is Null':

In the Expression Dialog box, enter:

     =if(vDuplicateOppsToggle = 1, $(vShowDuplicateOpps), $(vHideDuplicateOpps))

This expression will look at the value your button gives to vDuplicateOppsToggle and then uses dollar sign expansion to create a dimension. If the value is 1, then it simply supplies the Opportunity.Key. If it's 0, then it only brings back the Opportunity.Key that doesn't have a Opportunity.Lost Reason of 'Duplicate Opportunity'.

Step Three: The Button

Create a new button. Add a Set Variable Action:

Enter vDuplicateOppsToggle as the Variable. For the Value, enter:

     =If(vDuplicateOppsToggle = 1,0,1)

Next, enter:

     =If(vDuplicateOppsToggle = 1,'Hide Duplicates','Show Duplicates')

in the Text expression box:

Click OK.

Step Four: coup de grâce

Layout and format to taste. Click on the button and you'll notice that the 'Duplicate Opportunities' are gone, but the Nulls remain:

Two observations on this tip that you need to consider:

1. Only 'layout and format to taste' if you have good taste; if you don't, get help
2. You may think that some of my sentences are run-on, but they're not, they are concise, dammit! (slapping hand on table)