topic Re: Table not found when creating qvd file in QlikView

Table not found when creating qvd file

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table not found when creating qvd file

dominicmander — Mon, 19 Sep 2016 15:23:55 GMT

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

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

Re: Table not found when creating qvd file

rubenmarin — Mon, 19 Sep 2016 15:24:32 GMT

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.

Re: Table not found when creating qvd file

awhitfield — Mon, 19 Sep 2016 15:29:32 GMT

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

Re: Table not found when creating qvd file

Anonymous — Mon, 19 Sep 2016 15:45:43 GMT

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?

Re: Table not found when creating qvd file

dominicmander — Mon, 19 Sep 2016 16:28:08 GMT

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

Re: Table not found when creating qvd file

Anonymous — Mon, 19 Sep 2016 18:08:41 GMT

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.

Re: Table not found when creating qvd file

Anonymous — Mon, 19 Sep 2016 19:04:59 GMT

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

Re: Table not found when creating qvd file

rubenmarin — Tue, 20 Sep 2016 07:32:49 GMT

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

Re: Table not found when creating qvd file

dominicmander — Tue, 20 Sep 2016 08:14:59 GMT

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.

Re: Table not found when creating qvd file

Anonymous — Tue, 20 Sep 2016 12:57:30 GMT

Dominic,

I really appreciate your time in explaining my sample script. Now I understand. QV really forces me to think "differently" as my professional career is a MSSQL DBA. This is why I said I only have one column which is common in my tables. My tables being the sources without having any JOINS operated on yet. After I did the "drop table Cost", I was left with only the Product table which has all the fields on it with 8 rows (including dupes). The dupes exist due to how the code was written in above.

Thanks to everyone who responded !!

Re: Table not found when creating qvd file

dominicmander — Tue, 20 Sep 2016 13:58:14 GMT

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.

Re: Table not found when creating qvd file

Anonymous — Tue, 20 Sep 2016 14:12:07 GMT

Thanks for the tip about adding "exit script;" I just tried it. It's indeed very helpful. It's like "debug" in other programming languages.