Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
FROM SO_SOH soh
LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER;
Hi
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
Hi,
you can use either jonathan's "NoConcatenate".
or you can use "REPLACE only".
Regards,
Phani
(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;
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
Hi
Perhaps QV does not like the table name SO - I suggest that you enclose it with square brackets [SO].
Jonathan
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!
Hi
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