10 Replies Latest reply: Feb 5, 2015 2:06 PM by Rob Wunderlich

# Joined Table Shows up with records split within table (and other) objects

I'm hoping someone can see what I am missing here.  For some reason I'm having an issue with one table.

I have joined two tables using a key field and to simplify (and for troubleshooting purposes) have removed all but one additional field in each table and have removed all other tables.  The script runs fine and when complete I can preview my table (Ctrl-T), which appears to be in good working order.  (See below.)

As you can see, there are values in both of the non-key fields in each record, making it seem that the two tables were joined successfully (cleanly) and correctly.  This looks good to me.

But when trying to view this information in a simple Table object, the rows are split, where the "Regular_Hours_Init" and "Regular_Expense_Init" field values show up on different rows, with the same key field for each, as shown below.

I have never had this problem before and cannot figure out what is causing this to occur.  I assume that the underlying table within QlikView is correct, based on the 1st image, above, but am not sure why the records are showing up split in this manner.  It's almost like the two original tables are being concatenated instead of being joined.  Is there a table/object/document or other setting that is causing this behavior?  It only seems to be occuring with this table for me.

The script in place is below.  It executes with no errors.

OA_Payroll_Input_File_Init:
date(date#([End Date],'MMDDYYYY'),'YYYYMMDD') & [Where Worked] & right([Job Code],5) & Right('0000'&[Employee ID],5) as Table_Link_Init,
Num(DLLRS,'0.00') as Regular_Expense_Init
FROM
[\\<servername>\FinancialPrivate\Data\Finance-Secure\Transfer\to BRAIN\Payroll - BiWeekly\Biweekly Dllrs*.XLS]
(
biff, embedded labels, header is 5 lines, table is [BiWkly Dllrs$]) Where Not IsNull([Where Worked]); Join (OA_Payroll_Input_File_Init) LOAD date(date#([End Date],'MMDDYYYY'),'YYYYMMDD') & [Where Worked] & right([Job Code],5) & Right('0000'&[Employee ID],5) as Table_Link_Init, Num(HOURS,'0.0') as Regular_Hours_Init FROM [\\<servername>\FinancialPrivate\Data\Finance-Secure\Transfer\to BRAIN\Payroll - BiWeekly\Biweekly Hrs*.XLS] ( biff, embedded labels, header is 5 lines, table is [BiWkly Hrs$])
Where Not IsNull([Where Worked]);

If anyone has any suggestions for me on this, I would greatly appreciate it.  It could be something simple that I am just overlooking...

Thank you all.

• ###### Re: Joined Table Shows up with records split within table (and other) objects

One additional note: If I take the "join" statement out, the tables link together properly on the key field AND show up correctly in the table object.  (ie. Only one record per Table_Link_Init value.)  I would think that confirms that the key field has matching values between the two tables, but as soon as I add the "join" back, the record splits again.

• ###### Re: Joined Table Shows up with records split within table (and other) objects

When you hover over the fields in the table viewer, what is the information density of each?

-Rob

• ###### Re: Joined Table Shows up with records split within table (and other) objects

Mostly 100%, but a few have 99+%.

• ###### Re: Joined Table Shows up with records split within table (and other) objects

"Mostly.."? I was only asking about the "Regular_Hours_Init" and "Regular_Expense_Init" fields. What are their densities? Is there really just three fields in your model when you made the charts above?

• ###### Re: Joined Table Shows up with records split within table (and other) objects

Sorry - I had added in the other fields and thought I had the issue resolved, but was wrong...

The info density for the 3 fields listed above is:

Regular_Expense_Init - 54%

Regular_Hours_Init - 53%

There is a small % of records that would not match between the two data sets, but I want to keep those records in the table.  The info density does make it seem like it is duplicating the key and actually creating two records where they should actually be joined into one, doesn't it?

• ###### Re: Joined Table Shows up with records split within table (and other) objects

It looks to me as if the Table_Link_Init fields are different between the two tables -- not matches. Examine keys from each table.

-Rob

• ###### Re: Joined Table Shows up with records split within table (and other) objects

I don't see a way to attach a QVW within the discussion thread...  Does that have to be done in a new thread?

• ###### Re: Joined Table Shows up with records split within table (and other) objects

While editing, select "Use Advanced editor" (in upper right) to be able to add attachments.

-Rob

• ###### Re: Joined Table Shows up with records split within table (and other) objects

Can I know What script did you use for using that simple Table?

I think If you use Distinct for the first Field should be helped