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

[jslancas]

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:
LOAD
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.

[jslancas]

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.

[rwunderlich]

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

-Rob

[#4]

Jeff Lancaster

please post sample file

[jslancas]

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

[rwunderlich]

"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?

[jslancas]

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:

Table_Link_Init - 100%

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?

[jslancas]

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

[rwunderlich]

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

-Rob

[#10]

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