topic Re: Fields Not Matching Properly in QlikView

Fields Not Matching Properly

Mon, 19 Jan 2015 22:50:56 GMT

Hey all,

So, I have two tables. One is a list of numbers associated with data and the other is the list of numbers, but with a name attached to it. The way I run my matching is I pull in the fields 1 at a time based on a Type identifier as such:

However, for some reason I still find blanks in my data. Here is a screenshot of one such blank (sorry about the size):

Column 2 is the blank and column 5 is it's number. Here is a screen shot of that number in the list I make to give each number a name (Columns go Type, ID, Name):

There are no spaces or blank rows above this entry.

Lastly, here is my script for the load. What is going on!

I have tripled check the syntax and am pretty sure I have everything set up correctly. Type = first column in second picture, ID = second column in second picture, and Name = third column in second picture.

Re: Fields Not Matching Properly

Peter_Cammaert — Tue, 20 Jan 2015 01:10:05 GMT

Problems with data quality?

Two possible causes:

Some entries in one table do not have matching entries in the other table (works both ways with NULLs in different fields)
Some key values have both entries, but may lack information in one or more fields. That is if you consider blanks (empty strings) lack of information.

You can use table boxes and key value duplication (create a copy of the key value in each table, with a local name) to check for correct linkage.

Best,

Peter

Re: Fields Not Matching Properly

Tue, 20 Jan 2015 03:59:57 GMT

For the first one, that's what I thought too. But after throwing it into excel and filtering to check for blanks...there are none. I am thinking of just left joining the names to the numbers (might be easier?).

Re: Fields Not Matching Properly

robert_mika — Tue, 20 Jan 2015 04:36:34 GMT

Just a thought

If the Excel numbers in one column are formatting as Numbers and in the second as Text that may not work

Re: Fields Not Matching Properly

Peter_Cammaert — Tue, 20 Jan 2015 09:01:56 GMT

Just a suggestion to cause missing data to stand out:

Select one of the IdXxxx tables that refuses to produce valid Names in some rows.
Add a copy of the Id and call it IdCopyXxxxxx for example (replace the Xxxxx with....)
Add a copy of the corresponding Id in AdWordsData, and call it something comparable, say IdCopyXxxxxData.
Now after reading in the Actual data, add rows to the IdXxxx table for every value of IdCopyXxxxxData that doesn't exist in IdCopyXxxxx. Set the Name value to 'Undefined' or something.

Reload and check if the blank cells are being filled.

A shorter technique is to do the first three steps of the preceding list and create a tabble box with fields ga:adwordsXxxxxID, IdCopyXxxxxx and IdCopyXxxxxData and check for key values that do not produce perfect associations. For those values, either the data is missing, or - as Robert says - the keys do not match (for example when in the first copy data is left-aligned, and in the second copy data is right-aligned)

Best,

Peter

Re: Fields Not Matching Properly

Thu, 22 Jan 2015 22:25:51 GMT

Actually figured it out. Even though it is a tab deliminated txt file. There was a row that had a stray quotations, which was causing the rest of the data to not get pulled in.