problems with sum

[#1]

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?

[#2]

i tried to do an distinct INV_INVNUM which is my invoice number, and got a error message and so i tried to do a group by action and got the following error

This the error message:

[#3]

i tried to do an distinct INV_INVNUM which is my invoice number, and got a error message and so i tried to do a group by action and got the following error

This the error message:

[reddy-s]

Hi Jhonatan,

When you use a group by all the fields have to aggregated for it to work .

So use the below syntax:

KEY_TABLE:

load KEY_INV_INVNUM,

       only(<field1>),

       only(<field2>),

      .

      .

      etc..

      resident KEY_TABLE_TEMP group by KEY_INV_INVNUM;

[#5]

Hi i tried to write it down  like this:

KEY_TABLE:

//טבלת מפתחות

LOAD

KEY_INV_INVNUM,

ONLY (<CUST_CUST>),

ONLY (<FAMILY_FAMILY>),

ONLY (<PART_PART>),

ONLY (<AGENT_AGENT>),

ONLY (<BRANCH_BRANCH>),

ONLY (<DATE>),

CUST_CUST&'_'&AGENT_AGENT&'_'&BRANCH_BRANCH&'_'&PART_PART&'_'&DATE  AS SALES_KEY

RESIDENT KEY_TABLE_TEMP

GROUP BY KEY_INV_INVNUM

;

DROP TABLE KEY_TABLE_TEMP;

and got this error:

[#6]

Hi first of all thanks for your tips,

second:

i wrote doen this:

KEY_TABLE:

//טבלת מפתחות

LOAD

KEY_INV_INVNUM,

ONLY (CUST_CUST),

ONLY (FAMILY_FAMILY),

ONLY (PART_PART),

ONLY (AGENT_AGENT),

ONLY (BRANCH_BRANCH),

ONLY (DATE),

ONLY (CUST_CUST&'_'&AGENT_AGENT&'_'&BRANCH_BRANCH&'_'&PART_PART&'_'&DATE ) AS KEY_SALES_KEY

RESIDENT KEY_TABLE_TEMP

GROUP BY KEY_INV_INVNUM

;

DROP TABLE KEY_TABLE_TEMP;

and althogh my model viewer looks like this:

i still have the same problem...

[reddy-s]

Hi Jhonatan,

You have forgotten to rename the fields:

KEY_TABLE:

//טבלת מפתחות

LOAD

KEY_INV_INVNUM,

ONLY (CUST_CUST) as CUST_CUST,

ONLY (FAMILY_FAMILY) as FAMILY_FAMILY,

ONLY (PART_PART) as PART_PART,

ONLY (AGENT_AGENT) as AGENT_AGENT,

ONLY (BRANCH_BRANCH) as BRANCH_BRANCH,

ONLY (DATE) AS DATE,

ONLY (CUST_CUST&'_'&AGENT_AGENT&'_'&BRANCH_BRANCH&'_'&PART_PART&'_'&DATE ) AS KEY_SALES_KEY

RESIDENT KEY_TABLE_TEMP

GROUP BY KEY_INV_INVNUM

;

DROP TABLE KEY_TABLE_TEMP;

Do you still have duplicated records?

[#8]

still has it

[reddy-s]

Hi Jhonatan,

I have made a sample example for you to refer to.

Check out if you are doing anything wrong.

- Sangram

[#10 · Accepted solution]

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!