topic Re: Re: table not found on resident load in QlikView

table not found on resident load

Tue, 02 Dec 2014 22:22:23 GMT

I have a table that I'm loading called assets.

There is also a table that I have called book_parts. For the book_parts, I'm doing a left join to assets.

I am trying to evaluate the data in book_parts to determine if there is more than one sequence number for an asset so that I can use this flag at expression time on my charts.

I have the following snippet & keep getting table not found error. any help with this?????

assets:

LOAD CompanyID,

AssetID,

AcquisitionDate,

Year(AcquisitionDate) as Fiscal_Year,

Month(AcquisitionDate) as Fiscal_Period

SQL Select * from asset;

book_parts:

left join(assets)

LOAD CompanyID,

AssetID,

Sequence,

BookID,

PlaceServ,

AcqValue

SQL select * from bookparts;

Left Join(assets)

Load CompanyID,

AssetID,

BookID,

IF( BookID = '1' AND MAX(Sequence)= 0, '0',

IF( BookID = '1' AND MAX(Sequence)> 0 , '>0', null()))as SeqRange

Resident book_parts;

Here's my load that keeps giving me the error: table not found

I even tried giving it the name book_parts1: but that didn't work either.

Re: table not found on resident load

Peter_Cammaert — Tue, 02 Dec 2014 22:27:52 GMT

A left join tries to merge the data you load to the table between parentheses. So where should the name book_parts go if you're not creating a new table, just adding to assets?

Peter

Re: table not found on resident load

Peter_Cammaert — Tue, 02 Dec 2014 22:34:58 GMT

Change your code as follows:

Delete the left join from the second LOAD. A new resident table called book_parts will be created (you may have to add prefix NOCONCATENATE)
After the second LOAD, insert a new LEFT JOIN (Assets) LOAD that merges columns from book_parts to assets.
Then add your original third LOAD

Peter

Re: table not found on resident load

Tue, 02 Dec 2014 22:43:54 GMT

assets:

LOAD CompanyID,

AssetID,

AcquisitionDate,

Year(AcquisitionDate) as Fiscal_Year,

Month(AcquisitionDate) as Fiscal_Period

SQL Select * from asset;

book_parts:

noconcatenate

LOAD CompanyID,

AssetID,

Sequence,

BookID,

PlaceServ,

AcqValue

SQL select * from bookparts;

left join(assets)

book_parts

Left Join(assets)

Load CompanyID,

AssetID,

BookID,

IF( BookID = '1' AND MAX(Sequence)= 0, '0',

IF( BookID = '1' AND MAX(Sequence)> 0 , '>0', null()))as SeqRange

Resident book_parts;

do you mean like this????? I think I'm a little lost on the second step.

Re: Re: table not found on resident load

Peter_Cammaert — Tue, 02 Dec 2014 22:47:48 GMT

Change the third statement into:

left join(assets)

LOAD * RESIDENT

book_parts;

Best,

Peter

PS Sorry, posting is becoming a real hassle on this forum. Qlik Community simply refuses to accept replies these days.

Re: Re: table not found on resident load

Peter_Cammaert — Tue, 02 Dec 2014 22:52:56 GMT

Note that the last statement will produce further errors. You can't use the Max() function without a GROUP BY clause. LOADs work record-by-record. A single record cannot look beyond its own fields to see whether this is the maximum sequence number.

Re: Re: table not found on resident load

Peter_Cammaert — Tue, 02 Dec 2014 22:58:07 GMT

What is the thinking behind the last statement?

Re: Re: table not found on resident load

Tue, 02 Dec 2014 23:00:28 GMT

I got it Peter; thanks for your help.