Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have this table :
Custs:
//טבלת לקוחות
LOAD
CUSTNAME AS CUST_CUSTNAME,
CUST AS CUST_CUST,
ADDRESS AS CUST_ADDRESS,
CURRENCY AS CUST_CURRENCY,
CUSTDES AS CUST_CUSTDES
FROM [lib://CLINTON_SAMPLE/customers.qvd]
(qvd);
and i am trying to run this :
Inv_Sales_temp: //טבלת מכירות כללית
LOAD
Date (IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
IVNUM AS INV_INVNUM,
DOC AS INV_DOC,
IV,
QPRICE AS INV_QPRICE,
TOTPRICE AS INV_TOTPRICE,
"CURRENCY" AS INV_CURRENCY,
CUST AS INV_CUST,
DEBIT,
VAT AS INV_VAT,
UDATE AS INV_UDATE,
PRINTED AS INV_PRINTED,
FINAL AS INV_FINAL,
ORD AS INV_ORD ,
DISCOUNT,
DISPRICE AS INV_DISCOUNT,
PAYDATE AS INV_PAYDATE,
FNCTRANS AS INV_FNCTRANS,
BOOKNUM AS INV_BOOKNUM,
AGENT AS INV_AGENT,
TYPE AS INV_TYPE,
CHECKING AS INV_CHECKING,
IMPFILE AS INV_IMPFILE,
STORNOFLAG AS INV_STORNOFLAG,
BRANCH AS INV_BRANCH
FROM [lib://CLINTON_SAMPLE/Invoices.qvd]
(qvd);
//פרטי ת.יומן
Left join
LOAD
FNCTRANS AS INV_FNCTRANS,
FNCNUM,FINAL as FNC_FINAL,
CURDATE AS FNC_CURDATE,
FNCDATE,
EXCHANGE3 AS FNC_EXCHANGE3,
CURRENCY3 AS FNC_CURRENCY3,
IVNUM AS FNC_IVNUM,
EXCHANGE2 AS FNC_EXCHANGE2
FROM [lib://CLINTON_SAMPLE/Fnctrans.qvd]
(qvd)
Where FINAL ='Y';
left Join (Inv_Sales_temp)
LOAD
CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES
Resident Custs;
but i am getting this :
Table not found: left Join (Inv_Sales_temp) LOAD CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES Resident Custs
i dont realiy understand why.... since i have wrote down custs...
jhonatan ben ami wrote:
still getting this :
Table not found: left Join (Inv_Sales_temp) LOAD CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES Resident Custs
Have you really added the Exit script; statement beofre the JOIN?
Then the script should not proceed to the failing statement.
Jhonatan,
try putting an
Exit Script;
statement before your last LOAD
left Join (Inv_Sales_temp)
LOAD
CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES
Resident Custs;
Then reload your app and open the data model view.
Double check that both tables referenced in your LOAD statement, 'Inv_Sales_temp' and 'Custs' really exist.
still getting this :
Table not found: left Join (Inv_Sales_temp) LOAD CUST_CUST AS INV_CUST, CUST_CUSTNAME AS INV_CUSTNAME,CUST_CUSTDES AS INV_CUSTDES Resident Custs
I doubt this table Inv_Sales_temp is not getting created.
The reason for this can be there might be already a similar table with all the fields of Inv_Sales_temp and it is getting concatenated to that table.
Can you check that?
Cheers,
Naresh
i have the same problem with this table:
Accdates
LOAD
CURDATE AS ACC_CURDATE,
COSTFLAG AS ACC_COSTFLAG,
COSTDATE AS ACC_COSTDATE
FROM [lib://Clinton key/Accdates.qvd]
(qvd);
and trying to preform left join:
INV_SALES_TEMP:
LOAD
Date(IVDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
IVNUM AS INV_INVNUM,
DOC AS INV_DOC,
IV,
QPRICE AS INV_QPRICE,
TOTPRICE AS INV_TOTPRICE ,
CURRENCY AS INV_CURRENCY,
CUST AS CUST_CUST,
DEBIT,
FINAL AS INV_FINAL,
ORD AS INV_ORD,
DISCOUNT AS INV_DISCOUNT,
PAYDATE AS INV_PAYDATE,
FNCTRANS AS INV_FNCTRANS,
BOOKNUM AS INV_BOOKNUM,
AGENT AS INV_AGENT,
"TYPE" AS INV_TYPE,
CHECKING AS INV_CHECKING,
IMPFILE AS INV_IMPFILE,
STORNOFLAG AS INV_STORNOFLAG ,
BRANCH AS INV_BRANCH
FROM [lib://Clinton key/Invoices.qvd]
(qvd)
where FINAL = 'Y'
and STORNOFLAG <> ('Y')
and Date (IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2013'
and
(TYPE='A' //חשבונית מס
OR TYPE='C' //חשבונית מרכזת
OR TYPE='F'
OR TYPE='E'); //חשבונית מס קבלה; //חשבונית חול;
//פרטיי תנועת יומן
LEFT JOIN (INV_SALES_TEMP)
LOAD
FNCTRANS AS INV_FNCTRANS,
FNCNUM,
FINAL AS FNC_FINAL,
CURDATE AS FNC_CURDATE,
FNCDATE,
EXCHANGE3 AS FNC_EXCHANGE3,
CURRENCY3 AS FNC_CURRENCY3,
IVNUM AS FNC_IVNUM,
EXCHANGE2 AS FNC_EXCHANGE2
FROM [lib://Clinton key/Fnctrans.qvd]
(qvd) Where FINAL ='Y';
//שורות החשבונית
LEFT JOIN (INV_SALES_TEMP)
LOAD
IV,
PART AS IV_PART,
PRICE AS IV_PRICE,
QUANT/1000 AS IV_QUANT,
QPRICE AS IV_QPRICE,
"CURRENCY" AS IV_CURRENCY,
"LINE" AS IV_LINE,
ORDI AS IV_ORDI,
"T$PERCENT" AS IV_PERCENT,
UDATE AS IV_UDATE,
KLINE AS IV_KLINE,
TQUANT/1000 AS IV_TQUANT,
TUNIT,
TRANS AS IV_TRANS,
TOTPERCENT AS IV_TOTPERCENT ,
DUTYCOST AS IV_DUTYCOST,
COSTC AS IV_COSTC,
(IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,
TYPE AS IV_TYPE,
CREDITCOST AS IV_CREDITCOST ,
BRANCH AS IV_BRANCH,
IVDATE AS IV_IVDATE,
ICURRENCY AS IV_ICURRENCY ,
IEXCHANGE AS IV_IEXCHANGE ,
CREDITFLAG AS IV_CREDITFLAG ,
AGENT AS IV_AGENT,
COMMISSION AS IV_COMMISSION
FROM [lib://Clinton key/InvoiceItems.qvd]
(qvd)
WHERE
(TYPE='A' //חשבונית מס
OR TYPE='C' //חשבונית מרכזת
OR TYPE='F' //חשבונית חול
OR TYPE='E' )//חשבונית מס קבלה
;
// //נתונים נוספים לשורת חשבונית
LEFT JOIN (INV_SALES_TEMP)
LOAD
IV,
KLINE AS IV_KLINE,
COSTC2 AS IVA_COSTC2 ,
COSTC3 AS IVA_COSTC3,
COSTC4 AS IVA_COSTC4,
COSTC5 AS IVA_COSTC5,
TOTPRICE AS IVA_TOTPRICE ,
IVTAX AS IVA_IVTAX,
EXTCOST AS IVA_EXTCOST,
EXTCOST2 AS IVA_EXTCOST2,
BONUSFLAG AS IVA_BONUSFLAG
FROM [lib://Clinton key/InvoiceItemsa.qvd]
(qvd);
// נתונים מכרטיס לקוח
LEFT JOIN (INV_SALES_TEMP)
LOAD
CUST_CUST,
CUST_CUSTNAME AS INV_CUSTNAME,
CUST_CUSTDES AS INV_CUSTDES
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
// CUST_AGENTNAME
RESIDENT
CUSTOMERS;
// //נתונים מכרטיס פריט משורשרים לטבלת המכירות
LEFT JOIN (INV_SALES_TEMP)
LOAD
PART_PART AS IV_PART,
// PART_COSTQUANT,
PART_COST as IV_PART_COST,
PART_FAMILYNAME as IV_FNAME
// PART_SECONDCOST
RESIDENT PARTS;
// נתונים משורות התנועה המקשורת לחשבונית - עלות וכדומה
LEFT JOIN (INV_SALES_TEMP)
LOAD
(IF(ISNULL(TRANS)=-1,0, TRANS)) AS IV_TRANS,
DOC AS TRANS_DOC,
PART AS TRANS_PART,
// QUANT/1000 AS TRANS_QUANT,
(QUANT/1000 *(IF (TYPE='N',(-1),1))) AS TRANS_QUANT,
ORDI AS TRANS_ORDI,
CUST AS TRANS_CUST,
PRICE AS TRANS_PRICE,
IV,
COST AS TRANS_COST,
// מייצג עלות TRANS_COST
SECONDCOST AS TRANS_SECONDCOST,
BRANCH AS TRANS_BRANCH,
CURRENCY AS TRANS_CURRENCY,
COGFLAG AS TRANS_COSTFLAG,
CURDATE AS TRANS_CURDATE
FROM [lib://Clinton key/Trans.qvd]
(qvd);
//שער מטבע לתאריך החשבונית ולמטבע השורה
Left JOIN (INV_SALES_TEMP)
LOAD
CUR_CURRENCY AS INV_CURRENCY,
CUR_EXCHANGE
Resident CURRENCIES;
LEFT JOIN (INV_SALES_TEMP)
LOAD
Date (CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
CURREG_CURRENCY AS INV_CURRENCY,
CURREG_EXCHANGE
Resident CURREG;
left JOIN (INV_SALES_TEMP)
LOAD
CUR_EXCHANGE AS CUR_EXCHANGE2
Resident CURRENCIES Where CUR_CURRENCY = -2;
LEFT JOIN (INV_SALES_TEMP)
LOAD
Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY') AS INV_IVDATE,
CURREG_EXCHANGE AS CURREG_EXCHANGE2
Resident CURREG Where CURREG_CURRENCY = -2;
// POSSIBLE EURO COIN
Left JOIN (INV_SALES_TEMP)
LOAD
CUR_EXCHANGE AS CUR_EXCHANGE3
Resident CURRENCIES
Where CUR_CURRENCY =$(vForeignCurrency);
// שיניתי כי לא עבד בפעם הראשונה לא זהה את השדה
// POSSIBLE EURO COIN
left JOIN (INV_SALES_TEMP)
LOAD
Date ( CURREG_CURDATE/60/24+32143,'DD/MM/YYYY')AS INV_IVDATE ,
CURREG_EXCHANGE AS CURREG_EXCHANGE3
Resident CURREG
Where CURREG_CURRENCY =$(vForeignCurrency);
Left join (INV_SALES_TEMP)
Load
ACC_COSFLAG,
DATE ((num(MAX (ACC_COSTDATE)))/60/24 +32143,'DD/MM/YYYY') AS MAXACC_COSTDATE
Resident Accdates
Where ACC_COSTDATE <> 0
GROUP By ACC_COSFLAG;
Drop table Accdates;
the error message:
Table not found
The error occurred here:
Left join (INV_SALES_TEMP)
Load
ACC_COSFLAG,
DATE ((num(MAX (ACC_COSTDATE)))/60/24 +32143,'DD/MM/YYYY') AS MAXACC_COSTDATE
Resident Accdates
Where ACC_COSTDATE <> 0
GROUP By ACC_COSFLAG
Hi Jhonatan,
I would suggest you to add "No Concatenate" before the load statement of the table which is missing.
This would allow you to avoid Auto Concatenate.
YOU MEAN writting it like this?
Accdates:
No Concatenate
LOAD
CURDATE AS ACC_CURDATE,
COSTFLAG AS ACC_COSTFLAG,
COSTDATE AS ACC_COSTDATE
FROM [lib://Clinton key/Accdates.qvd]
(qvd);
still getting this error:
Hi Jhonatan,
Use the No Concatenate before the load of the table INV_SALES_TEMP
so it would be like this:
No Concatenate
INV_SALES_TEMP:
LOAD
x,
y,
z
from ......