Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey everyone i have a problem , i am buliding a sales report,
once i am trying to sum the incomes coulmn for some reason its duplicates my incomes ?
anyone have suggetions what should i check?
Hey everyone i have changed the script to the following:
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,
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/2010'
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,
// IVCOST AS IV_IVCOST,
(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);
// isn't oblogated
// נתונים מכרטיס לקוח
LEFT JOIN (INV_SALES_TEMP)
LOAD
CUST_CUST AS INV_CUST,
// CUST_CUSTNAME AS INV_CUSTNAME,
CUST_CUSTDES AS INV_CUSTDES
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
// CUST_AGENTNAME
RESIDENT
CUSTOMERS;
// NOT ALL IS isn't oblogated
//נתונים מכרטיס פריט משורשרים לטבלת המכירות
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)
;
// 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;
INV_SALES:
LOAD
Distinct INV_CUST,
INV_CUSTDES,
INV_IVDATE AS IVDATE,
// INV_TOTPRICE,
// IF (INV_TOTPRICE > 0 AND DEBIT ='D','pos','neg') AS INV_TOTSIGN,
INV_INVNUM,
// CUST_AGENTNAME AS INV_AGENTNAME,
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
//שיניתי בכדי למנוע לופ//
IV_AGENT,
IV_BRANCH,
INV_TYPE,
IV_PART,
// IV_PRICE,
TRANS_COST,
// הוספתי בכדי לקבל את העלות//
IV_QUANT,
IV_QUANT*IF(DEBIT ='C',-1,1) AS RIV_QUANT,
IV_PERCENT,
// IV_FAMILYFTCODE,
// IV_COMMISSION,
// IV_IVCOST,
(IF(ISNULL(IV_TRANS) =-1,0,IV_TRANS)) AS IV_TRANS,
// IV_CREDITFLAG,
// מכפיל לזיכויים//
// ככה מוודאים שחשבוניות זיכוי בר
// (IF(DEBIT='C',(-1),1)) AS IVCAL_CREDIT,
// מחיר בשקלים לשורה
(IV_IVCOST * (IF (DEBIT ='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))
AS NIS_TOTPRICE,
// מחיר בדולרים לשורה
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2))
AS DOLLAR_TOTPRICE,
// // מחיר במטבע שלישי
// POSSIBLE EURO COIN
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(CURREG_EXCHANGE3 > 0 ,
CURREG_EXCHANGE3 , CUR_EXCHANGE3)
AS CURRENCY_TOTPRICE,
//חישוב רווח ועלות לשורה
// במרכאות
// TRANS_COST,TRANS_COSTFLAG,DATE(TRANS_CURDATE/60/24 +32143,'DD/MM/YYYY') AS TRANS_CURDATE ,MAXACC_COSTDATE,IV_PART_COST,
//עלות בשקל לתנועה
// ILS FOR A TRANSCATION
IF ((IsNull(TRANS_COST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
(IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1)))) AS NIS_COST,
// //עלות בדולר לשורת תנועה
IF ((IsNull(TRANS_SECONDCOST)= 0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <= MAXACC_COSTDATE),
TRANS_SECONDCOST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
PART_SECONDCOST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT ='C',(-1),1)) * (IF (IV_CREDITFLAG = 'Y',0,1))) AS DOLLAR_COST,
// // // עלות במטבע שלישי לשורת תנועה
// POSSIBLE EURO COIN
(IF ((IsNull(TRANS_SECONDCOST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1))) ) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) AS CURRENCY_COST,
// // עלויות ייצוא
// // המשתנה מייצג את השער שברצוננו לקחת האם מתוך תיק הייצוא (שורת החשבונית) או לפי שער המרה לאותו היום
// ILS
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))))) AS NIS_DUTYCOST,
// DOLLAR
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_DUTYCOST,
// POSSIBLE EURO COIN
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_DUTYCOST,
//עלויותת נוספות
//ILS
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) AS NIS_EXTCOST,
// DOLLAR
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_EXTCOST,
// POSSIBLE EURO COIN
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_EXTCOST,
/*
*/
//מפתח בטבלת מכירות
INV_CUST&'_'&IV_AGENT&'_'&IV_BRANCH&'_'&IV_PART&'_'&INV_IVDATE AS SALES_KEY
RESIDENT INV_SALES_TEMP
WHERE
(INV_TYPE='A' //חשבונית מס
OR INV_TYPE='C' //חשבונית מרכזת
OR INV_TYPE='F' //חשבונית חול
OR INV_TYPE='E' )
// and Date (INV_IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'
// ORDER BY
// IV_AGENT,
// IV_PART,
// INV_IVDATE
;
DROP TABLE INV_SALES_TEMP;
and the most important thing was to do: Distinct INV_CUST,
thank you all for helping!
Pure speculation -> It could be because you might be joining another table in the script which is leading to duplicate incomes. You can either leave your table unjoined and let QlikView handle the join or you can use an aggregate function.
unfortunately it seems that the problem is still there ....
Have you already ensured that there are no duplicates in your data source?
trying to show my script:
ACCDATES:
LOAD
CURDATE AS ACC_CURDATE,
COSTFLAG AS ACC_COSTFLAG,
COSTDATE AS ACC_COSTDATE
FROM [lib://Clinton key/Accdates.qvd]
(qvd);
DOCS:
LOAD
DOC AS DOC_DOC,
TYPE,
T$PERCENT,
DOCNO,
CUST,
AGENT
FROM [lib://Clinton key/Docs.qvd]
(qvd);
AGENTS:
LOAD
AGENT AS AGENT_AGENT,
AGENTCODE ,
AGENTNAME AS AGENT_AGENTNAME
FROM [lib://Clinton key/Agents.qvd]
(qvd);
BRANCHES:
LOAD
COMPANY&'_'&BRANCH AS BRANCH_BRANCH,
BRANCHNAME,
BRANCHDES,
COMPANY AS BRANCH_COMPANY
FROM [lib://Clinton key/Branches.qvd]
(qvd);
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;
Parts_TMP: //טבלת פריטים
LOAD
PART AS PART_PART,
PARTNAME,
TYPE AS PART_TYPE ,
PARTDES,
UNIT AS PART_UNIT,
PARTSTAT AS PART_STATUS,
PRICE AS PART_PRICE ,
LASTPRICE AS PART_LASTPRICE ,
COST AS PART_COST,
FAMILY AS PART_FAMILY,
SECONDCOST AS PART_SECONDCOST,
COSTQUANT AS PART_COSTQUANT
FROM [lib://Clinton key/parts.qvd]
(qvd);
Left Join(Parts_TMP)
LOAD
FAMILY AS PART_FAMILY,
FAMILYNAME as PART_FAMILYNAME,
SELLFLAG as PART_FAMSELLFLAG,
FAMILYTYPE AS FAMILY_TYPE
FROM [lib://Clinton key/Family.qvd]
(qvd);
PARTS: // טבלת פריטים קבוע
LOAD
PART_PART,
PARTNAME as PART_PARTNAME,
PART_TYPE,
PARTDES,
PART_COST,
PART_FAMSELLFLAG,
PART_FAMILY,
PART_FAMILYNAME,
// PART_FAMILYDES,
FAMILY_TYPE,
PART_SECONDCOST,
PART_COSTQUANT,
PART_STATUS
RESIDENT Parts_TMP
Where PART_STATUS <> -2;
;
DROP Table Parts_TMP;
CURRENCIES:
//מטבעות ושערי חליפין
LOAD
NAME,
CODE AS CUR_CODE,
CURRENCY AS CUR_CURRENCY ,
EXCHANGE AS CUR_EXCHANGE ,
EXCHDATE AS CUR_EXCHDATE
FROM [lib://Clinton key/Currency.qvd]
(qvd);
CURREG:
//שערי חליפין
LOAD
CURRENCY AS CURREG_CURRENCY,
EXCHANGE AS CURREG_EXCHANGE ,
CURDATE AS CURREG_CURDATE
FROM [lib://Clinton key/CurReg.qvd]
(qvd);
and what is your expression?
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/2010'
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,
// IVCOST AS IV_IVCOST,
(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);
// 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;
INV_SALES:
LOAD
CUST_CUST AS INV_CUST,
INV_IVDATE AS IVDATE,
INV_TOTPRICE,
IF (INV_TOTPRICE > 0 AND DEBIT ='D','pos','neg') AS INV_TOTSIGN,
INV_INVNUM,
// CUST_AGENTNAME AS INV_AGENTNAME,
// במקרה שלנו לא אפקטיבי רוצים את הסוכן מהחשבונית עצמה
//שיניתי בכדי למנוע לופ//
IV_AGENT,
IV_BRANCH,
INV_TYPE,
IV_PART,
IV_PRICE,
TRANS_COST,
//הוספתי בכדי לקבל את העלות//
IV_QUANT,
IV_QUANT*IF(DEBIT ='C',-1,1) AS RIV_QUANT,
IV_PERCENT,
// IV_FAMILYFTCODE,
IV_COMMISSION,
IV_IVCOST,
(IF(ISNULL(IV_TRANS) =-1,0,IV_TRANS)) AS IV_TRANS,
IV_CREDITFLAG,
// מכפיל לזיכויים//
// ככה מוודאים שחשבוניות זיכוי בר
(IF(DEBIT='C',(-1),1)) AS IVCAL_CREDIT,
// מחיר בשקלים לשורה
(IV_IVCOST * (IF (DEBIT ='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))
AS NIS_TOTPRICE,
// מחיר בדולרים לשורה
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2))
AS DOLLAR_TOTPRICE,
// // מחיר במטבע שלישי
// POSSIBLE EURO COIN
(IV_IVCOST * (IF (DEBIT='C',(-1),1)) * (IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3 = INV_CURRENCY,FNC_EXCHANGE3,
IF(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))) / IF(CURREG_EXCHANGE3 > 0 ,
CURREG_EXCHANGE3 , CUR_EXCHANGE3)
AS CURRENCY_TOTPRICE,
//חישוב רווח ועלות לשורה
// במרכאות
// TRANS_COST,TRANS_COSTFLAG,DATE(TRANS_CURDATE/60/24 +32143,'DD/MM/YYYY') AS TRANS_CURDATE ,MAXACC_COSTDATE,IV_PART_COST,
//עלות בשקל לתנועה
// ILS FOR A TRANSCATION
IF ((IsNull(TRANS_COST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
(IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1)))) AS NIS_COST,
// //עלות בדולר לשורת תנועה
IF ((IsNull(TRANS_SECONDCOST)= 0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <= MAXACC_COSTDATE),
TRANS_SECONDCOST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
PART_SECONDCOST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT ='C',(-1),1)) * (IF (IV_CREDITFLAG = 'Y',0,1))) AS DOLLAR_COST,
// // // עלות במטבע שלישי לשורת תנועה
// POSSIBLE EURO COIN
(IF ((IsNull(TRANS_SECONDCOST)=0 OR TRANS_COSTFLAG = 'M') OR (IV_TRANS <> 0 AND TRANS_CURDATE <=MAXACC_COSTDATE),
TRANS_COST * (IF (TRANS_QUANT * IV_TQUANT < 0.0,(-1),1)),
IV_PART_COST * IV_QUANT / PART_COSTQUANT * (IF (DEBIT='C',(-1),1)) * (IF (IV_CREDITFLAG='Y',0,1))) ) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) AS CURRENCY_COST,
// // עלויות ייצוא
// // המשתנה מייצג את השער שברצוננו לקחת האם מתוך תיק הייצוא (שורת החשבונית) או לפי שער המרה לאותו היום
// ILS
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE ))))))) AS NIS_DUTYCOST,
// DOLLAR
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_DUTYCOST,
// POSSIBLE EURO COIN
(IV_DUTYCOST * (IF (DEBIT='C',(-1),1)) * (IF(($(vEXPORTEXCHANGE)=1) AND IV_DUTYCOST <> 0,IV_IEXCHANGE,(IF(INV_CURRENCY = (-1) ,1,IF(FNC_CURRENCY3=INV_CURRENCY,FNC_EXCHANGE3,
if(CURREG_EXCHANGE > 0 , CURREG_EXCHANGE, CUR_EXCHANGE )))))))/ IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_DUTYCOST,
//עלויותת נוספות
//ILS
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) AS NIS_EXTCOST,
// DOLLAR
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(FNC_EXCHANGE2 > 0 , FNC_EXCHANGE2, IF(CURREG_EXCHANGE2 > 0 ,
CURREG_EXCHANGE2 , CUR_EXCHANGE2)) As DOLLAR_EXTCOST,
// POSSIBLE EURO COIN
(IVA_EXTCOST * (IF (DEBIT='C',(-1),1)) * (IF(IV_CREDITFLAG ='Y',0,1))) / IF(CURREG_EXCHANGE3 > 0 ,CURREG_EXCHANGE3 , CUR_EXCHANGE3) As CURRENCY_EXTCOST,
//מפתח בטבלת מכירות
CUST_CUST&'_'&IV_AGENT&'_'&IV_BRANCH&'_'&IV_PART&'_'&INV_IVDATE AS SALES_KEY
RESIDENT INV_SALES_TEMP
WHERE
(INV_TYPE='A' //חשבונית מס
OR INV_TYPE='C' //חשבונית מרכזת
OR INV_TYPE='F' //חשבונית חול
OR INV_TYPE='E' )
// and IV_FNAME <> '900'
// 900 דגים וציוד
// חשבונית מס קבלה
// and Date (INV_IVDATE/60/24+32143,'DD/MM/YYYY') >= '01/01/2010'
ORDER BY
IV_AGENT,
IV_PART,
INV_IVDATE;
DROP TABLE INV_SALES_TEMP;
KEY_TABLE_TEMP: // טבלת מפתחות זמנית
LOAD AGENT_AGENT
Resident AGENTS;
CONCATENATE
LOAD
BRANCH_COMPANY AS COMP,
BRANCH_BRANCH
RESIDENT BRANCHES;
CONCATENATE
load
CUST_CUST ,
CUST_CTYPE AS CTYPE
RESIDENT CUSTOMERS;
CONCATENATE
Load
PART_PART
RESIDENT PARTS;
CONCATENATE
LOAD
INV_CUST AS CUST_CUST,
IV_PART AS PART_PART,
IV_AGENT AS AGENT_AGENT,
IV_BRANCH AS BRANCH_BRANCH,
IVDATE AS DATE
RESIDENT INV_SALES;
KEY_TABLE:
//טבלת מפתחות
LOAD
CUST_CUST,
PART_PART,
AGENT_AGENT,
BRANCH_BRANCH,
DATE,
CUST_CUST&'_'&AGENT_AGENT&'_'&BRANCH_BRANCH&'_'&PART_PART&'_'&DATE AS SALES_KEY
RESIDENT KEY_TABLE_TEMP;
DROP TABLE KEY_TABLE_TEMP;
LOAD distinct IVDATE AS DATE
Resident INV_SALES
where IVDATE >= $(vStart_Date);
Concatenate
load Distinct TRANS_CURDATE Resident Returns ;
CALENDAR: //תאריכון כולל הזמנות וחשבוניות
LOAD
distinct DATE,
YEAR (DATE) as YEAR,
MONTH (DATE) as MONTH,
WeekDay ( DATE)as WEEKDAY,
Day (DATE)as DAY,
week(DATE) as WeekNum,
'Q' &'-'& ceil(month(DATE)/3) as Quarter,
'H' &'/'& ceil(month(DATE)/6) as Half_Year,
'W' &'/'& ceil(DAY(DATE)/4) as Week_Month,
Date( MonthStart(DATE),'MMM-YYYY') as YearMonth,
if(DayNumberOfYear(today())>=DayNumberOfYear(DATE),'YTD') as YTD
Resident Calendar_build
where IsNull(DATE)=0;
;
DROP Table Calendar_build;
DROP Table DOCS;
DROP Table CURRENCIES;
DROP Table CURREG;
when i am trying to get the sum of the income field IV_IVCOST this field duplicate itself:
meaning iv_ivcost= 100
sum( IV_IVCOST)=200
Hi Jhonatan,
Can you check if the records are duplicated in the parts.qvd itself? I mean in the previous layer of QVD generation. I don't see any issue with the script.