topic Re: Use of Resident in Load Script Table Not Found why? in QlikView

Use of Resident in Load Script Table Not Found why?

Tue, 15 May 2012 19:39:38 GMT

On one tab, I load (successfully) the following table, shortened for brevity. Like I say, there is no issue loading the SO table.

SO:
LOAD
SO_NUMBER as %SOKey,

LINE as SO.LINE,
LOT as SO.LOT,
SUB_LOT as SO.SUB_LOT;

SQL SELECT

soh.SO_NUMBER,

soh.LINE,

soh.LOT,

soh.SUB_LOT

FROM SO_SOH soh
LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER;

//the following gives the "Table Not Found" error. I have this code on the next adjacent tab...

SO_Inv:

REPLACE LOAD
LINE as SO_Inv.LINE,
LOT as SO_Inv.LOT,
SUB_LOT as SO_Inv.SUB_LOT
RESIDENT SO;

// I am simply trying to create a table called SO_Inv from columns on the original SO table. ANY IDEAS? thanks.

Re: Use of Resident in Load Script Table Not Found why?

jonathandienst — Tue, 15 May 2012 20:17:31 GMT

But is the SO table loading any data? The table will not be created if the SQL query returns no data.

If the answer is Yes, does the script load another table with the same fields as SO before this? This would result in the data being concatenated to the other table and SO would not be created. In this case, you put a NoConcatenate statement

SO:

NoConcatenate

LOAD ...

Do you need the Replace?

Hope that helps

Jonathan

Re: Use of Resident in Load Script Table Not Found why?

phaneendra_kunc — Tue, 15 May 2012 20:26:27 GMT

Hi,

you can use either jonathan's "NoConcatenate".

or you can use "REPLACE only".

Regards,

Phani

Re: Use of Resident in Load Script Table Not Found why?

Tue, 15 May 2012 21:25:11 GMT

(How come when I click reply on Jonathans' post, it says I'm replying to Phani?) Anyway..Thanks for the responses.

Yes, SO has, and continues to load 2 million plus records. And No, there are no tables that load before or after these two tabs (containing SO and SO_Inv) that read from either SO or its source, soh.

I added the NOCONCATENATE and get the same error. The REPLACE was just there as a testing option.

I took REPLACE out and still get the error. Not working, "table not found". Very odd.

Here is the absolute full SO script, SO_Inv remains as above. I appreciate any direction. :>>>>>

// ===================================================================================================
// SO (Joned SOH and SOD)
// ===================================================================================================

SO:

NoConcatenate
LOAD

SO_NUMBER as %SOKey,
AutoNumberHash128(LOT & SUB_LOT) as %POSOKey,
AutoNumberHash128(LOT & SUB_LOT) as %SOCSKey,
TRIP_NUMBER as %TripKey,

PRODUCT_CODE as SO.ProductKey, // Product Key
SHIP_TO as %CustomerKey, // Customer Key
CUSTOMER_CODE as %OwnerKey, // Owner Key - corrsponds to the vendor code
WAREHOUSE,
date(date#(EXPECTED_DELIVERY_DATE, 'YYYYMMDD'), 'MM/DD/YYYY') as "Expected Delivery Date",
ORIGINATION_CODE,
CUSTOMER_PO_NUMBER,
STATUS_O_S_T_V,
BILL_DATE,
CUSTOMER_PO_2ND_REFERENCE,
SODID,
LINE as SO.LINE,
SHIPPED_QUANTITY as SO.SHIPPED_QUANTITY,
WEIGHT,
LOT as SO.LOT,
SUB_LOT as SO.SUB_LOT,
UNIT_PRICE,
ORDER_QUANTITY as SO.ORDER_QUANTITY,
STATUS_O_C_T_V
;
SQL SELECT
soh.SOHID,
soh.SO_NUMBER,
soh.TRIP_NUMBER,
soh.SHIP_TO,
soh.SHIP_TO_STATE,
soh.SHIP_TO_ZIP,
soh.WAREHOUSE,
soh.SHIP_TO_CITY,
soh.CUSTOMER_CODE,
soh.EXPECTED_DELIVERY_DATE,
soh.ORIGINATION_CODE,
soh.CUSTOMER_PO_NUMBER,
soh.STATUS_O_S_T_V,
soh.BILL_DATE,
soh.CUSTOMER_PO_2ND_REFERENCE,
sod.SODID,
sod.PRODUCT_CODE,
sod.LINE,
sod.SHIPPED_QUANTITY,
sod.WEIGHT,
sod.LOT,
sod.SUB_LOT,
sod.UNIT_PRICE,
sod.ORDER_QUANTITY,
sod.STATUS_O_C_T_V

FROM SO_SOH soh
LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER
//WHERE EXPECTED_DELIVERY_DATE >= '$(StartDate)'                                        // ??
;

// -----------
//SO2TripMap:
//MAPPING LOAD DISTINCT
//     %SOKey as A,
//     %TripKey as B
//RESIDENT SO;

Re: Use of Resident in Load Script Table Not Found why?

Anonymous — Wed, 16 May 2012 00:11:51 GMT

If you have 2mill records in SO, you should be able to perform a resident load.

I wonder if its not a syntax issue (with Qlikview). For example, I find that inline tables can't have a number at the start of the field, but its ok if you enclose them in square brackets.

I wonder if you could look at removing the special characters in your SO table for starters. or encapsulate them in square brackets. [%SOKey] AS A

Re: Use of Resident in Load Script Table Not Found why?

jonathandienst — Wed, 16 May 2012 07:02:06 GMT

Perhaps QV does not like the table name SO - I suggest that you enclose it with square brackets [SO].

Jonathan

Re: Use of Resident in Load Script Table Not Found why?

Wed, 16 May 2012 13:34:49 GMT

Thanks again for all your help. I tried brackets, no good.

I have whittled the script down. [SO] still loads records as before. Same error on SO_Inv however.

SO_Inv tab now has:

SO_Inv:

LOAD
SO_NUMBER
// LINE as SO_Inv.LINE
//LOT as SO_Inv.LOT,
// SUB_LOT as SO_Inv.SUB_LOT
RESIDENT [S0];

-----------------------------------------------

SO tab now has:

[SO]:

LOAD
SO_NUMBER ;

SQL

SELECT
soh.SOHID,
soh.SO_NUMBER,
soh.TRIP_NUMBER,
soh.SHIP_TO,
soh.SHIP_TO_STATE,
soh.SHIP_TO_ZIP,
soh.WAREHOUSE,
soh.SHIP_TO_CITY,
soh.CUSTOMER_CODE,
soh.EXPECTED_DELIVERY_DATE,
soh.ORIGINATION_CODE,
soh.CUSTOMER_PO_NUMBER,
soh.STATUS_O_S_T_V,
soh.BILL_DATE,
soh.CUSTOMER_PO_2ND_REFERENCE,
sod.SODID,
sod.PRODUCT_CODE,
sod.LINE,
sod.SHIPPED_QUANTITY,
sod.WEIGHT,
sod.LOT,
sod.SUB_LOT,
sod.UNIT_PRICE,
sod.ORDER_QUANTITY,
sod.STATUS_O_C_T_V
FROM SO_SOH soh
LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER;

-------------------------------

Error:

Table not found
SO_Inv:
LOAD
SO_NUMBER

RESIDENT [S0]

------------------ Next step would be to offload what I can from QV to sql server, from where the source data comes!

Re: Use of Resident in Load Script Table Not Found why?

Sun, 29 Jun 2014 06:03:20 GMT

I am having the same trouble atm...

But looking at your code:

RESIDENT [S0];

It looks to me that this is a ZERO and not an O