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!

[kkorynta]

Morning! (bump)

[#3 · Accepted solution]

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)

[kkorynta]

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.

[#5]

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.

[kkorynta]

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

Thanks!

[godfreydaniels]

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

Set NullInterpret = '';