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?

[sunny_talwar]

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.

[#3]

[#4]

unfortunately it seems that the problem is still there ....

[sunny_talwar]

Have you already ensured that there are no duplicates in your data source?

[#6]

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

[sunny_talwar]

and what is your expression?

[#8]

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;

[#9]

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

[reddy-s]

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.