16 Replies Latest reply: Dec 14, 2015 7:05 AM by jhonatan ben ami RSS

    a table that is writen but getting unfound....

    jhonatan ben ami

      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...

        • Re: a table that is writen but getting unfound....
          Stefan Wühl

          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.

            • Re: a table that is writen but getting unfound....
              jhonatan ben ami

              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

                • Re: a table that is writen but getting unfound....
                  Stefan Wühl

                  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.

                    • Re: a table that is writen but getting unfound....
                      jhonatan ben ami

                      hey Swel what do you mean by exit comment :

                       

                      again i have this table:

                      CUSTOMERS:

                      LOAD

                      CUSTNAME AS CUST_CUSTNAME,

                      CUST AS CUST_CUST,

                      CUSTDES AS CUST_CUSTDES,

                      CTYPE AS CUST_CTYPE,

                      TERRITORY AS CUST_TERRITORY,

                      CUSTSTAT AS CUST_CUSTAT

                      // AGENT AS CUST_AGENTNAME  

                      // במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

                      FROM [lib://Clinton key/customers.qvd]

                      (qvd)

                      WHERE CUSTSTAT <> -5;

                       

                      then i am trying to do this :

                      //נתונים נוספים לשורת חשבונית

                      Left Join

                      LOAD IV,

                      KLINE AS IV_KLINE,

                      COSTC2 AS IVA_COSTSC2,

                      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_SAMPLE/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;

                       

                      and i keep getting

                       

                      1.PNG

                        • Re: a table that is writen but getting unfound....
                          Sangram Reddy

                          Hi Jhonatan,

                           

                          Do you have more script above the Customers table. Because I can't see a table named Inv_Sales_temp and this could be the cause of the error.

                            • Re: a table that is writen but getting unfound....
                              jhonatan ben ami

                              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, // "T$USER",

                              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

                              IV,

                              PART AS IV_PART,

                              PRICE AS IV_PRICE,

                              QPRICE AS IV_QPRICE,

                              QUANT/1000 AS IV_QUANT,

                              "CURRENCY" AS IV_CURRENCY,

                              LINE AS IV_LINE,

                              ORDI AS IV_ORDI,

                              UDATE AS IV_UDATE,

                              KLINE AS IV_KLINE,

                              TQUANT/1000 AS IV_TQUANT,

                              TUNIT AS TUNIT,

                              TRANS AS IV_TRANS,

                              TOTPERCENT AS IV_TOTPERCENT,

                              (IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,

                              TYPE AS IV_TYPE,

                              //PRSOURCE,

                              //STORNOFLAG,

                              COSTC AS IV_COSTC,

                              CREDITCOST AS IV_CREDITCOST,

                              //ACCOUNT,

                              BRANCH AS IV_BRANCH,

                              IVDATE AS IV_IVDATE,

                              ICURRENCY AS IV_ICURRENCY,

                              IEXCHANGE AS IV_IEXCHANGE,

                              //COUNTRY,

                              CREDITFLAG AS IV_CREDITFLAG,

                              AGENT AS IV_AGENT,

                              COMMISSION AS IV_COMMISSION,

                              DUTYCOST AS IV_DUTYCOST     

                              FROM [lib://CLINTON_SAMPLE/InvoiceItems.qvd]

                              (qvd);

                               

                              //נתונים נוספים לשורת חשבונית

                              Left Join

                              LOAD IV,

                              KLINE AS IV_KLINE,

                              COSTC2 AS IVA_COSTSC2,

                              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_SAMPLE/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

                              CUST_CUST AS INV_CUST,

                              CUST_CUSTNAME AS INV_CUSTNAME,

                              CUST_CUSTDES AS INV_CUSTDES

                              Resident Custs;*/

                               

                              //נתונים מכרטיס הפריט משורשרים לטבלת המכירות

                              left Join (Inv_Sales_temp)

                              LOAD

                              PARTNAME AS IV_PARTNAME,

                              PART_PART AS IV_PART,

                              PART_PRICE AS IV_PART_PRICE,

                              PART_SECONDPRICE AS IV_PART_SECONDPRICE,

                              PART_LASTPRICE AS IV_PARTLASTPRICE,

                              PART_LASTCURRENCY AS IV_PART_LASTCURRENCY, 

                              PART_COSTQUANT,

                              PART_COST as IV_PART_COST,

                              PART_SECONDCOST

                              // PART_MGCAT as IV_PART_MGCAT,

                              // PART_MGDEP as IV_PART_MGDEP

                              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,

                              ORDI AS TRANS_ORDI,

                              CUST AS TRANS_CUST,

                              PRICE AS TRANS_PRICE,

                              IV,

                              COST AS 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_SAMPLE/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);

                               

                              // LINE 183 IN THE SALES CHART//

                              LEFT JOIN(INV_SALES_TEMP)

                              LOAD

                              ACC_COSTFLAG,

                              DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE

                              RESIDENT ACCDATES

                              WHERE ACC_COSTDATE<>0

                              GROUP BY ACC_COSTFLAG;

                               

                              DROP TABLE ACCDATES;

                                • Re: a table that is writen but getting unfound....
                                  jhonatan ben ami

                                  this is the first part:

                                  CUSTOMERS:

                                  LOAD

                                  CUSTNAME AS CUST_CUSTNAME,

                                  CUST AS CUST_CUST,

                                  CUSTDES AS CUST_CUSTDES,

                                  CTYPE AS CUST_CTYPE,

                                  TERRITORY AS CUST_TERRITORY,

                                  CUSTSTAT AS CUST_CUSTAT

                                  // AGENT AS CUST_AGENTNAME  

                                  // במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה

                                  FROM [lib://Clinton key/customers.qvd]

                                  (qvd)

                                  WHERE CUSTSTAT <> -5;

                                   

                                  Part_Balance: // מלאי לפריט לפי מחסנים

                                  Load

                                  PART AS BALANCE_PART,

                                  BALANCE AS BALANCE_BALANCE,

                                  WARHS AS BALANCE_WARHS

                                  FROM [lib://CLINTON_SAMPLE/Balance.qvd]

                                  (qvd);

                                   

                                  LEFT JOIN (Part_Balance)

                                  LOAD

                                  WARHS as BALANCE_WARHS,

                                  WARHSDES AS BALANCE_WARHSDES,

                                  WARHSNAME AS BALANCE_WARHSNAME,

                                  TYPE AS WARHS_TYPE

                                  FROM [lib://CLINTON_SAMPLE/Warhs.qvd]

                                  (qvd);

                                   

                                  Parts: //טבלת פריטים

                                  LOAD

                                  PARTNAME,

                                  PART AS PART_PART,

                                  TYPE AS PART_TYPE,

                                  PARTDES,

                                  UNIT AS PART_UNIT,

                                  STATUS AS PART_STATUS,

                                  PRICE AS PART_PRICE,

                                  SECONDPRICE AS PART_SECONDPRICE ,

                                  // MGSP_CATEGORY as PART_MGCAT,

                                  // MGSP_DEPARTMENT AS PART_MGDEP,

                                  LASTPRICE AS PART_LASTPRICE,

                                  LASTCURRENCY AS PART_LASTCURRENCY,

                                  COST AS PART_COST,

                                  FAMILY AS PART_FAMILY,

                                  SECONDCOST AS PART_SECONDCOST,

                                  COSTQUANT AS PART_COSTQUANT

                                  FROM [lib://CLINTON_SAMPLE/parts.qvd]

                                  (qvd);

                                   

                                  LEFT JOIN (Parts)

                                  LOAD

                                  BALANCE_PART AS PART_PART,

                                  sum( BALANCE_BALANCE) AS PART_BALANCE

                                  Resident Part_Balance

                                  where WARHS_TYPE <> 'W'

                                  Group by BALANCE_PART;

                                   

                                  PartFam: // משפחות מוצר

                                  LOAD

                                  FAMILY AS FAMILY_FAMILY,

                                  FAMILYNAME,

                                  FAMILYDES,

                                  FAMILYTYPE,

                                  TYPE AS FAMILY_TYPE

                                  FROM [lib://CLINTON_SAMPLE/Family.qvd]

                                  (qvd);

                                   

                                  Branches: //סניפים

                                  LOAD

                                  BRANCH AS BRANCH_BRANCH ,

                                  BRANCHNAME,

                                  BRANCHDES

                                  FROM [lib://CLINTON_SAMPLE/Branches.qvd]

                                  (qvd);

                                   

                                  Currencies: // מטבעות ושערי חליפין

                                  LOAD

                                  NAME,

                                  CODE AS CUR_CODE,

                                  CURRENCY AS CUR_CURRENCY,

                                  EXCHANGE AS CUR_EXCHANGE,

                                  EXCHDATE AS CUR_EXDATE

                                  FROM [lib://CLINTON_SAMPLE/Currency.qvd]

                                  (qvd);

                                   

                                  Curreg: //שערי חליפין

                                  LOAD

                                  CURRENCY AS CUREG_CURRENCY,

                                  EXCHANGE AS CURREG_EXCHANGE,

                                  CURDATE AS CURREG_CURDATE

                                  FROM [lib://CLINTON_SAMPLE/CurReg.qvd]

                                  (qvd);

                                   

                                  ACCDATES:

                                  LOAD

                                  CURDATE AS ACC_CURDATE,

                                  COSTFLAG AS ACC_COSTFLAG,

                                  COSTDATE AS ACC_COSTDATE

                                  FROM [lib://CLINTON_SAMPLE/Accdates.qvd]

                                  (qvd);

                                   

                                  this is the second part:

                                  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, // "T$USER",

                                  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

                                  IV,

                                  PART AS IV_PART,

                                  PRICE AS IV_PRICE,

                                  QPRICE AS IV_QPRICE,

                                  QUANT/1000 AS IV_QUANT,

                                  "CURRENCY" AS IV_CURRENCY,

                                  LINE AS IV_LINE,

                                  ORDI AS IV_ORDI,

                                  UDATE AS IV_UDATE,

                                  KLINE AS IV_KLINE,

                                  TQUANT/1000 AS IV_TQUANT,

                                  TUNIT AS TUNIT,

                                  TRANS AS IV_TRANS,

                                  TOTPERCENT AS IV_TOTPERCENT,

                                  (IF(ISNULL(IVCOST)=-1,0,IVCOST)) AS IV_IVCOST,

                                  TYPE AS IV_TYPE,

                                  //PRSOURCE,

                                  //STORNOFLAG,

                                  COSTC AS IV_COSTC,

                                  CREDITCOST AS IV_CREDITCOST,

                                  //ACCOUNT,

                                  BRANCH AS IV_BRANCH,

                                  IVDATE AS IV_IVDATE,

                                  ICURRENCY AS IV_ICURRENCY,

                                  IEXCHANGE AS IV_IEXCHANGE,

                                  //COUNTRY,

                                  CREDITFLAG AS IV_CREDITFLAG,

                                  AGENT AS IV_AGENT,

                                  COMMISSION AS IV_COMMISSION,

                                  DUTYCOST AS IV_DUTYCOST     

                                  FROM [lib://CLINTON_SAMPLE/InvoiceItems.qvd]

                                  (qvd);

                                   

                                  //נתונים נוספים לשורת חשבונית

                                  Left Join

                                  LOAD IV,

                                  KLINE AS IV_KLINE,

                                  COSTC2 AS IVA_COSTSC2,

                                  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_SAMPLE/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

                                  CUST_CUST AS INV_CUST,

                                  CUST_CUSTNAME AS INV_CUSTNAME,

                                  CUST_CUSTDES AS INV_CUSTDES

                                  Resident Custs;*/

                                   

                                  //נתונים מכרטיס הפריט משורשרים לטבלת המכירות

                                  left Join (Inv_Sales_temp)

                                  LOAD

                                  PARTNAME AS IV_PARTNAME,

                                  PART_PART AS IV_PART,

                                  PART_PRICE AS IV_PART_PRICE,

                                  PART_SECONDPRICE AS IV_PART_SECONDPRICE,

                                  PART_LASTPRICE AS IV_PARTLASTPRICE,

                                  PART_LASTCURRENCY AS IV_PART_LASTCURRENCY, 

                                  PART_COSTQUANT,

                                  PART_COST as IV_PART_COST,

                                  PART_SECONDCOST

                                  // PART_MGCAT as IV_PART_MGCAT,

                                  // PART_MGDEP as IV_PART_MGDEP

                                  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,

                                  ORDI AS TRANS_ORDI,

                                  CUST AS TRANS_CUST,

                                  PRICE AS TRANS_PRICE,

                                  IV,

                                  COST AS 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_SAMPLE/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);

                                   

                                  // LINE 183 IN THE SALES CHART//

                                  LEFT JOIN(INV_SALES_TEMP)

                                  LOAD

                                  ACC_COSTFLAG,

                                  DATE((NUM(MAX(ACC_COSTDATE)))/60/24+32143,'DD/MM/YYYY') AS MAXACC_COSTDATE

                                  RESIDENT ACCDATES

                                  WHERE ACC_COSTDATE<>0

                                  GROUP BY ACC_COSTFLAG;

                                   

                                  DROP TABLE ACCDATES;

                      • Re: a table that is writen but getting unfound....
                        Naresh Guntur

                        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

                          • Re: a table that is writen but getting unfound....
                            jhonatan ben ami

                            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