28 Replies Latest reply: Dec 15, 2015 7:40 AM by jhonatan ben ami RSS

    problems with sum

    jhonatan ben ami

      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?

        • Re: problems with sum
          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.

          • Re: problems with sum
            jhonatan ben ami

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

            • Re: problems with sum
              jhonatan ben ami

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

                • Re: problems with sum
                  Sunny Talwar

                  and what is your expression?

                  • Re: problems with sum
                    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 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;

                    • Re: problems with sum
                      Sangram Reddy

                      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.

                    • Re: problems with sum
                      jhonatan ben ami

                      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!