Force blank cells to be missing

[kkorynta]

Hi,

I haven't seen this type of behavior since I've started working with Qlikview. I have a field that's being stubborn.

This is a tax ID field that's on file for our various customers. The tax IDs are held in a separate table from the customer information table. Using a customer ID to join the two tables together, I've matched the Tax ID up with the customer.

The problem is, not all of our customers have a tax ID listed. When the two tables join, the customers without a tax ID aren't marked as missing - it's a blank field, presumably with " " in the cell. This is an issue because I'm trying to count the number of missing tax IDs. Without it marked as missing, the " " is still counted as 1.

I've tried doing a few things in the script for the initial load of the table (before the join):

num#([Tax ID]) as [Tax ID]

trim([Tax ID]) as [Tax ID]

text(trim([Tax ID]) as [Tax ID]

None of these seem to have any effect. I'm not quite sure what's going on in order to fix it.

Any help would be greatly appreciated.

Thanks!

[#2]

If Tax ID is loading as Null, you may try using Set Analysis:

Count({$-<Tax ID={'*'}>} Tax ID)

If Tax ID really is just a blank character, then it should be easier using Set Analysis:

Count({<Tax ID={' '}>} Tax ID)

There is a space between the single quotes there. It should give you the count of Tax IDs where Tax ID is a single space character.

[kkorynta]

I did give that a shot with no luck. The problem is, there's no way to tell how many spaces it's using.

And what's odd, is if I export it into excel, I click the 'blank cell' and look at the contents and there's nothing in there. But it still has a count of one. Now when I double click on it, the cell finally recognizes there's nothing in it.

[kkorynta]

Here's the export to give you an idea of what I'm talking about.

[#5]

Why not just trim() the tax id field on load? Then it would be null as expected.

Another idea is left join the tax ids into the customer table.

[kkorynta]

I did both of those. That was the first thing I said in this post.

[#7]

I'm sorry, I'm stuck with an old version of Excel. Could you post that as xls or csv?

Have you looked at a list box for Tax ID? Do you have a bunch of blank lines at the top? That would indicate that your field is storing spaces and varying numbers of spaces.

It seems odd that the data is coming in this way. Where is the original source of the data? Database, Excel? If the data really contains a differing number of spaces instead of single blanks or nulls, then it is probably a good idea to clean it up either in the original source or during your load into QlikView.

[kkorynta]

I do have a list box open. There's only one row of spaces. I know there's something fishy going on about the dataset overall because when i try to do a len([first name]), the names are different, but the length for all of them is 10, even after trimming them.

The original source was a query to our db server and saved in a .txt file. When I exported this excel file, i intentionally exported only the "blank" NPI to show how blank cells had a count value.

Luckily this file is small enough, i went ahead and opened it in excel and did went to the text to columns option and did a fixed width, which seems to do a good job at trimming and fixing oddities like this, saved that file and imported it into Qlikview.

This is supposed to be more of an exercise. The idea is that later, I will be given a much bigger data set (one that can't be opened in either excel or notepad) and I would use Qlikview to import and clean.

This isn't the first time I've seen something odd like this happen, however, I worked around it in the past. But there's going to come a point where working around it won't cut it. This is the first time I've seen it affect all fields in a data set though.

I'd still like to see if we can figure out what's going on and fix it.

[#9]

Since there is only one row of spaces, then all of your blank fields share the same value. You should be able to show only "null" Tax IDs in a table by selecting that blank line in the list box.

Looking at your Excel file, the blank Tax ID fields actually contain an empty string. There are no spaces in that field. Try this Set Analysis:

Count({<Tax ID={''}>} Tax ID)

Those are single quotes with NO space in between.

It's kind of hard to troubleshoot without being able to see how the data is imported into QlikView. When I load your Excel file into QlikView (using the Wizard, no modifications), I get the blank Tax ID values as Nulls. I can tell by making a chart and changing the Null symbol from - to N.

Have your tried (in your load):

If(IsNull([Taxonomy Code]), '-', [Taxonomy Code]) As Taxonomy Code

If you do that, you replace the Null values with the dash symbol and it should be easy to pull those values out using selections or Set Analysis.

[kkorynta]

count({Tax ID={''}>} Tax ID) works for post load.

The if isnull statement does not. I do find this quite odd. And even still, what's more odd about this, which I probably should have mentioned earlier, is that this field has 3 particular cases: ones with '' as you brought up, ones with '-' as missing (which I was expecting), and ones with an actual value.

I went ahead and uploaded the NPI data set so you can examine that one. Do note that whoever queried this data didn't realize that an auto delimiter was turned on, so when they wrote the query to include a second delimiter, it ended up double delimiting. In other words, the ~~ you'll see should only be ~.

Here's my load statement I've been working on currently. The first NPI table is the problem table in question.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Taxonomy List]:
NoConcatenate LOAD text(trim(@1)) as [Code Key],
text(trim(@2)) as [Taxonomy Code],
text(trim(@3)) as [Taxonomy Description]
FROM (txt, codepage is 1252, no labels, delimiter is ';', msq);

[NPI]:
LOAD text(trim(@1)) as [Customer Entity],
@3 as [Entity ID],
@5 as [Revision No],
@7 as [Entity Type],
if(Isnull(@9), '-', @9) as NPI,
text(trim(@1)) & text(trim(@3)) as [Special Key]
FROM (txt, codepage is 1252, no labels, delimiter is '~', msq);
/*
[NPI]:
LOAD A as [Customer Entity],
B as [Entity ID],
C as [Revision No],
D as [Entity Type],
E as NPI,
A & B as [Special Key]
FROM (ooxml, no labels, table is npi_20101209);
*/
[NPI 2]:
NoConcatenate Load [Special Key], max([Revision No]) as [Max Revision No], NPI, [Customer Entity]
Resident [NPI]
Group By [Special Key], NPI, [Customer Entity];

Drop Table NPI;

[Facility Provider]:
LOAD text(trim(@1)) as [Customer Entity],
@3 as [Staff Entity],
@5 as [Revision No],
@7 as [Last Name],
@9 as [First Name],
@11 as [Entity Qualifier],
@13 as Registered,
@15 as [Provider Type],
@17 as [Taxonomy Code],
@19 as [Facility Entity],
@20 as [Organization Name],
text(trim(@1)) & text(trim(@3)) as [Special Key]
FROM (txt, codepage is 1252, no labels, delimiter is '~', msq);

[Facility Provider 2]:
NoConcatenate Load [Special Key], max([Revision No]) as [Max Revision No], [Facility Entity], [Organization Name], [Customer Entity]
Resident [Facility Provider]
Group By [Special Key], [Facility Entity], [Organization Name], [Customer Entity];

Drop Table [Facility Provider];

[Rendering Provider]:
NoConcatenate LOAD text(trim(@1)) as [Customer Entity],
@2 as [Staff Entity],
@3 as [Revision No],
@4 as [Last Name],
@5 as [First Name],
@6 as [Entity Qualifier],
@7 as Registered,
@8 as [Provider Type],
text(trim(@9)) as [Code Key],
text(trim(@1)) & text(trim(@2)) as [Special Key]
FROM (txt, codepage is 1252, no labels, delimiter is ';', msq);

[Registered]:
NoConcatenate Load [Customer Entity] as [Reg Customer Entity],
[Staff Entity] as [Reg Staff Entity],
[First Name] & ' ' & [Last Name] as [Reg Name],
Registered
Resident [Rendering Provider];

[Rendering Provider 2]:
NoConcatenate Load [Special Key], max([Revision No]) as [Max Revision No], [Last Name], [First Name], [Entity Qualifier], [Code Key], [Customer Entity]
Resident [Rendering Provider]
Group By [Special Key], [Last Name], [First Name], [Entity Qualifier], [Code Key], [Customer Entity];

Drop Table [Rendering Provider];

Left Join ([Rendering Provider 2])
Load [Special Key], NPI
Resident [NPI 2];

[NPI Provider Facility Merge]:
NoConcatenate Load [Special Key], [Max Revision No], [Last Name], [First Name], [Entity Qualifier], [Code Key], NPI, [Customer Entity]
Resident [Rendering Provider 2];

Drop Tables [NPI 2], [Rendering Provider 2];

Left Join ([NPI Provider Facility Merge])
Load [Code Key], [Taxonomy Code], [Taxonomy Description]
Resident [Taxonomy List];

Drop Table [Taxonomy List];

Left Join ([NPI Provider Facility Merge])
Load [Special Key], [Organization Name], [Facility Entity]
Resident [Facility Provider 2];

Drop Table [Facility Provider 2];