table not found on resident load

[#1]

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.

[Peter_Cammaert]

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

[Peter_Cammaert · Accepted solution]

Change your code as follows:

1. 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)
2. After the second LOAD, insert a new LEFT JOIN (Assets) LOAD that merges columns from book_parts to assets.
3. Then add your original third LOAD

Peter

[#4]

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.

[Peter_Cammaert]

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.

[Peter_Cammaert]

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.

[Peter_Cammaert]

What is the thinking behind the last statement?

[#8]

I got it Peter; thanks for your help.