topic Force blank cells to be missing in QlikView

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 16:57:57 GMT

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!

Force blank cells to be missing

Mon, 13 Dec 2010 17:16:47 GMT

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.

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 17:33:23 GMT

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.

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 17:40:46 GMT

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

Force blank cells to be missing

Mon, 13 Dec 2010 19:11:09 GMT

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.

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 19:20:50 GMT

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

Force blank cells to be missing

Mon, 13 Dec 2010 20:48:21 GMT

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.

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 21:53:25 GMT

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.

Force blank cells to be missing

Mon, 13 Dec 2010 22:12:26 GMT

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.

Force blank cells to be missing

kkorynta — Mon, 13 Dec 2010 22:32:40 GMT

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

Force blank cells to be missing

kkorynta — Tue, 14 Dec 2010 16:18:22 GMT

Morning! (bump)

Force blank cells to be missing

Tue, 14 Dec 2010 17:12:48 GMT

Wow, that is an odd file. Delimited and Fixed Width at the same time. When I open it in a full text editor, the blank NPI codes have a bunch of spaces before the line breaks. As you can see in some of the other fields, there are padded spaces to make the fields a fixed length.

I loaded the file into QlikView and it's a little weird. In the load, the values don't seem to be treated as Nulls. If I use:

IsNull(@9) As NPI_Test

Every field returns a FALSE. Once the data is loaded though, the values are null in the expression.

I also tried:

@9='' As NPI_Test

A few fields show up as TRUE, but most show up as Null.

I don't even know if processing would help. Anyway, I can get all the null or blank fields by using:

If(IsNull(NPI) or NPI='', 'Missing', NPI)

Force blank cells to be missing

kkorynta — Tue, 14 Dec 2010 17:42:10 GMT

That will work. ^_^

Any idea what's going on in the source file that would cause this? Other than the issue with the guy who pulled the data forgetting delimiters were already auto assigned, that's generally how I get the data. I'm not sure why SQL+ decides to pad for fixed width, it's a little obnoxious. However, I'm aware of it, and if I need to match/join on a variable I just make sure they're trimmed and it's proper.Usually, Qlikview does a good job at auto cleaning the data. This is by far the most trouble I've seen it have.

Force blank cells to be missing

Tue, 14 Dec 2010 17:55:54 GMT

I'm stumped as to what is happening with that file. I took a subset of the data and processed it in a text editor to remove all spaces and convert the ~~ to commas. I then reloaded that as a csv and I still have some fields with blanks and some with nulls. I then added another comma to the end of every line to see if the end line character could be an issue. Same deal.

I'm looking at the processed text file now and I am sure that there are no spaces in the fields. Some of them still load null and some the empty string.

There doesn't seem to be anything that could cause this issue, yet there it is. Weird.

Force blank cells to be missing

kkorynta — Tue, 14 Dec 2010 18:11:05 GMT

Well, on the bright side, thanks to your help, I'll know how to handle it the next time it happens.

Thanks!

Re: Force blank cells to be missing

godfreydaniels — Thu, 23 Jan 2020 16:48:16 GMT

Not sure about from the text file used here, but this worked for me for a csv:

Set NullInterpret = '';