topic Re: a table that is writen but getting unfound.... in App Development

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

Wed, 09 Dec 2015 19:18:29 GMT

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

swuehl — Wed, 09 Dec 2015 19:28:46 GMT

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

Thu, 10 Dec 2015 06:02:29 GMT

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

NareshGuntur — Thu, 10 Dec 2015 07:51:25 GMT

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

Thu, 10 Dec 2015 07:54:04 GMT

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

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

Thu, 10 Dec 2015 08:31:41 GMT

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

reddy-s — Thu, 10 Dec 2015 09:14:19 GMT

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.

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

Thu, 10 Dec 2015 09:16:15 GMT

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);

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

Thu, 10 Dec 2015 09:18:05 GMT

still getting this error:

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

reddy-s — Thu, 10 Dec 2015 09:31:11 GMT

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

from ......

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

Thu, 10 Dec 2015 10:12:12 GMT

hey Sangram

first of all thank you for your kind help

i am getting the following error.

i would try to compare it to different thing i have done....

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

reddy-s — Thu, 10 Dec 2015 10:15:05 GMT

Hi Jhonathan,

I think you missed out to attach the error or script I guess.

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

swuehl — Thu, 10 Dec 2015 18:14:06 GMT

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

Mon, 14 Dec 2015 09:47:45 GMT

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

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

reddy-s — Mon, 14 Dec 2015 11:38:04 GMT

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

Mon, 14 Dec 2015 12:01:27 GMT

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

Mon, 14 Dec 2015 12:05:41 GMT

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;