Table not found when creating qvd file

[Anonymous]

Hi,

I'm learning Qlikview.  I tried to create the below sample code. 

When I tried to refresh the document, I get the following error:

Table not found

store FinalResult into C:\temp\FinalResult.qvd(qvd)

Can someone explain to me why and what I'm doing wrong?

Thank you.

Product:

load * inline

[

  ProductID, Desc

  1, MOUSE

  2, KEYBOARD

  3, DRIVE

  4, MEMORY

];

Cost:

load * inline

[

  ProductID, Cost

  1, 8.50

  2, 9.50

  3, 10.50

];

left join (Product)

load *

resident Cost;

FinalResult:

load *

resident Product;

store FinalResult into C:\temp\FinalResult.qvd(qvd);

[dominicmander]

Hi Sydney,

When two tables have the same structure (as your Product table after the left join and your FinalResult table will have), QlikView automatically concatenates (unions) the two tables so FinalResult never gets created.

You have 2 options

1. prevent the concatenation (put 'NOCONCATENATE' before 'load' in your FinalResult load.
2. just store the product table, or rename the product table if you prefer.

[rubenmarin]

Hi Sydney, maybe is using autoconcatenation becasuse the last table (left join with products) returns the same fields wich are loaded in FinalResult.

to avoid this you can use 'NoConcatenate':

FinalResult:

NoConcatenate load *

resident Product;

BTW, the FinalResult Table will have the same as the products table, and if you achive to load in different tables it will associate all fields from Products and FinalResult Tables.

[awhitfield]

Hi Sydney,

try this instead:

Product:

load * inline
[

ProductID, Desc
1, MOUSE
2, KEYBOARD
3, DRIVE
4, MEMORY
];

Left Join
Cost:
load * inline
[
ProductID, Cost
1, 8.50
2, 9.50
3, 10.50
];


Store * from Product into c:\Temp\FinalResult.qvd(qvd);

make sure c:\temp exists

Andy

[Anonymous]

Dominic,

When I placed "noconcatenate" as you suggested, it worked and I got this result which was expected.

ProductID	Desc	Cost
1	MOUSE	8.5
2	KEYBOARD	9.5
3	DRIVE	10.5
4	MEMORY

You said "QlikView automatically concatenates (unions) the two tables so FinalResult never gets created.".  I don't think I fully understand this.  UNION in sql means combining two sets of data having the same number of fields/columns.  So if QV "unions" my Product and FinalResult, I would expect eight rows in my result each having a duplicate unless QV drops the duplicates.  Can you clarify this please?

[dominicmander]

Correct.

So, if you removed the last line of your original script (store FinalResult....) and ran the script as it was (i.e. without the noconcatenate), and then look at the table viewer, you should see the Product table there and if you right click and select preview you should see it contains 8 rows.

To access the table viewer use the shortcut Ctrl+T or the second button from the right on the design toolbar (you should enable this by the way if you haven't ... it's very useful, right click in blank space by the toolbars at the top of the window and check 'design')

[Anonymous]

Dominic,

I'm exploring my script keeping your replies in mind.  Yes, the table viewer shows 8 rows on the Product table.

The Cost table is disabled.  When I opened the "$Syn_1_Table", I see ProductID and Cost.  My question how does QV know how to relate the "Desc" back to the ProductID??  I'm not getting the whole picture of how things are tied back.

Thanks for your help & time.

[Anonymous]

Also I don't understand why it created a synthetic key since I only have one columns (ProductID) which is common to both tables.  The Help says " tables have two or more fields in common"

https://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Scripting/synthetic-keys.htm

[rubenmarin]

Hi Sydney, the tables have two fields in common: 'ProductID' and 'Cost'.

To avoid the synthetic key you can:

- If the Cost field in both tables have the same value you can remove the field from one of the tables or remove the entire Cost table

, Rename one of the Cost Fields so they are no longer common fields

[dominicmander]

Sydney,

Maybe if I explain what the script is doing it will become clear.

Your first inline load creates the product table (with productID and desc).

Your second inline load creates the cost table (with productID and cost).

Your third load statement (with the left join) loads * from the cost table and joins it  to the products table (using whatever fields are common between the two i.e. productID ... so the product table now has productID and desc and cost, and the cost table remains unchanged and still in the data model).

In this case ... if the end result you want is one table with all three fields in it, then I would put the left join before the inline load for the cost table and never create the cost table as a table in it's own right ... just join it straight to product. The alternative would be to drop the cost table after you are done with it (DROP TABLE Cost;). There will be times when you want to perform some other transformations on a table before joining it which will mean you have to create a temporary table and then drop it, but it's always good to try to keep your script as efficient as possible and in this case it is not necessary.