How to eliminate system fields left over from a join.

[#1]

Hello,

I am a pretty new user and having an issue with something that I just can't seem to find anything about on the web.  I have actually been able to find answers to all my questions from previous posts, just not this one.

Ok, what am I doing wrong.  I have two tables that I have joined together with a Left Join.  The first table has about 4k records, the second has 22k, so it should remove all by the 4k matches to the first table in the new joined table, right?  In fact, it does, kind of.  When I pull just that data into a table box related to this table I can see just the 4k records.  However, if I look at the preview of the new table in the Table Viewer, it has 22k records.  It took me a while to figure out the discrepancy, but it is because of these system fields that start with a "$", in particular the $RowNo field.  It appears to have one of those for each row whether it was eliminated or not.

I might have been able to live with this but A) I am eventually going to have lots more records and don't want to waste the space, and B) I am trying to do calculations in the script off these rows with something like a PEEK function, but having all these dummy records in there is making that a lot harder.  What's more, i just feel like I must be doing something wrong.

Below is the part of the code in question in case that helps, as well as two screenshots of what it is producing.  You can see that the first, smaller table is actually a grouped portion of the original table, basically taking a weekly packaging BOM and reducing it to one line that represents to total cost of that BOM.

Thanks in advance for any thoughts anyone can provide.  This is driving me crazy.

//********************Creates a second table to aggregate the data to just one line per finished good item per plant per week ending.************************

PackagingFilesTemp3:

LOAD

  [Summed Total STD PK $] / [Summed FG Units Produced] as [Std Cost/Unit],

  *;

LOAD

  [FG Unique In Time],

  Avg([FG Units Produced]) as [Summed FG Units Produced],

  SUM([Total STD PK $] ) as [Summed Total STD PK $]

Resident PackagingFilesTemp2

Group By [FG Unique In Time];

//*********************Joins the aggregated data back with the original table to give all the fields plus the summed up information.*************************

Left Join

Load

  [Week Ending],

     Plant,

    [FG Item],

    [FG Unique In Time]

Resident PackagingFilesTemp2;

Drop Table PackagingFilesTemp2;

Here is a screenshot of a table box with JUST the fields from the table.  Looks ok as it is 1 record per finished good item number, plant and week ending.

Here is a second table box where I now pull in these system fields in.  Here you can see each finsihed good, plant and week ending have a number of lines, one for each line of the BOM, which all are duplicates now since they have been aggregated but the system fields are causing duplication.  This is how it looks in the Table Viewer preview too.

[Gysbert_Wassenaar]

Does any field in the tables you create have the same name as a system field?

edit: never mind, qlikview doesn't seem to like giving a field the same name as a system field. If you had done that you likely wouldn't be able to open the table viewer either.

[#3]

Thanks for the quick reply. I actually have no interest in using these system fields, but I can see they are causing the duplication of data in the table which is why I showed them in the table box. When I pull just the table fields the data LOOKS ok, just the 4k rows, but the table still has all 22k rows, thus lots of duplication. When I eventually load all my data there will be many millions of rows so I don't want that duplication, but it is also causing issues when I try to peek from one week to the one before (ie most of those duplicate rows and peeking to another duplicate rather than the prior week).

Does that make more sense? Sorry, maybe pulling them into the table box was confusing, just wanted to point out what I believe is the source of the duplication.

Sent from my Verizon Wireless 4G LTE smartphone

[Gysbert_Wassenaar · Accepted solution]

I don't think the system fields have anything to do with it. And a table box will never show duplicate records. Your join is probably not doing what you expect. A left join does not guarantee that the result table won't have more records than the left table:

T1:

LOAD * INLINE [

A, B

1, 2

1, 4

2, 6

2, 8

];

Left Join (T4)

LOAD * INLINE [

A, C

1, 10

1, 20

1, 30

1, 40

1, 50

1, 60

];

The result of this join is a table with 14 rows, not 4 rows.

I think you'll need have to take a good look at the data in key fields of the tables you're joining.

[#5]

Ahhhhhh, ok.  So, when I first read your response I thought that couldn't be it.  I had tested the associations and they worked ok, and my table had a key that was unique to each row, so where your example created multiple rows in the joined table it is because there is not unique values in that column A.

However, I was totally backwards in my thinking.  The first table in the LEFT JOIN had unique values, but the second table did not and that is why I was still getting the duplicates.

So, there is probably a more elegant way of doing this, but I had basically created the key that I used for the aggregation off Plant/Item/week ending, and those were really the only fields I needed to pull back into the aggregated table, so I just used a subfield function to split out the key back into 3 separate fields.  Worked perfectly.

Thank you so much Gysbert.  I feel like an idiot but you got me off this train of thought around the system fields and back to the real issue.