Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Table not found when creating qvd file

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);

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

No problem. It is a bit of a different way of thinking, but equally logical I feel.

One thing that I still find very useful even though I have been doing this for years is dropping an EXIT SCRIPT; statement in and reloading and then using the table viewer to see what state the data model is in at a certain stage.

On a side note, it helps me and helps others searching for solutions to their own questions if you can mark a question as answered/helpful. More info on that here.

View solution in original post

12 Replies
Highlighted
Partner
Partner

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.
Highlighted

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.

Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

Dominic,

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

ProductIDDescCost
1MOUSE8.5
2KEYBOARD9.5
3DRIVE10.5
4MEMORY

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?

Highlighted
Partner
Partner

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')

Highlighted
Creator III
Creator III

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.

qv1.jpg

Highlighted
Creator III
Creator III

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

Highlighted

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

Highlighted
Partner
Partner

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.