5 Replies Latest reply: Mar 26, 2013 10:03 AM by Gysbert Wassenaar RSS

    Resident load problem

      I have the below code in my script. Why does the resident load not working...?????I dont get any error but the table CustomersTotals is not getting created.

       

       

      CustomersTotalsTmp:

      LOAD

                Customer as CMCustomer,

                Currency as CMCurrency,

                Name,

                OutstOrdVal,

                CreditLimit,

                OutstandingTotal,

                TermsCode,

                DueDays,

                InvoicedTotal;

      SQL SELECT

                cust.Customer,

                cust.Name,

                cust.OutstOrdVal as OutstOrdVal,

                cust.CreditLimit,

                cust.OutstOrdVal + cbal.CurrentBalance1 as OutstandingTotal,

                cust.TermsCode,

                term.DueDays,

                curr.Currency,

                cbal.CurrentBalance1 as InvoicedTotal

                FROM SysproCompanyA.dbo.ArCustomer as cust

                LEFT JOIN SysproCompanyA.dbo.TblArTerms as term

                ON cust.TermsCode=term.TermsCode

                LEFT JOIN SysproCompanyA.dbo.TblCurrency as curr

                ON cust.Currency=curr.Currency

                LEFT JOIN SysproCompanyA.dbo.ArCustomerBal as cbal

                ON cust.Customer=cbal.Customer

                ORDER BY cust.Customer;

       

      LEFT JOIN(CustomersTotalsTmp)

      //CurrInvoice:

      LOAD

                Customer as CMCustomer,

                Invoice as InvoiceNumber,

                //InvoiceDate as CMInvoiceDate,

                date(floor(InvoiceDate))  as CMInvoiceDate,

                fabs(Interval( date(floor(InvoiceDate)) - date(today(),'YYYY-MM-DD') , 'dd')) as DayDiffrence,

                //Name as CMSalesPerson,

                upper(Name) as CMSALESPERSON,

                ProductClass as CMProductClass,

                upper(Branch)                    as CMBRANCH,

                Description as CMProductClassDescription,

                QtyInvoiced as CMQtyInvoiced,

                NetSalesValue as CMNetSalesValue,

                StockCode,

                TaxStatus,

                //Currency as CMCurrency,

                CurrentInvoice;

      SQL SELECT

                inv.Customer,

                inv.Invoice,

                inv.InvoiceDate,

                trnd.Salesperson,

                trnd.ProductClass,

                pd.Description,

                trnd.QtyInvoiced,

                trnd.NetSalesValue * inv.ConvRate as NetSalesValue,

                trnd.StockCode,

                cust.TaxStatus,

                cust.Currency,

                sp.Name,

                br.Description as Branch,

                inv.InvoiceBal1 AS CurrentInvoice

                FROM SysproCompanyA.dbo.ArInvoice AS inv

                LEFT JOIN SysproCompanyA.dbo.ArCustomer as cust

                ON inv.Customer=cust.Customer

                LEFT JOIN SysproCompanyA.dbo.ArTrnDetail as trnd

                ON inv.Invoice=trnd.Invoice

                LEFT JOIN SysproCompanyA.dbo.SalSalesperson as sp

                ON trnd.Salesperson=sp.Salesperson and trnd.Branch=sp.Branch

                LEFT JOIN SysproCompanyA.dbo.SalProductClassDes as pd

                ON trnd.ProductClass=pd.ProductClass

                LEFT JOIN SysproCompanyA.dbo.SalBranch as br

                ON cust.Branch=br.Branch

                WHERE inv.YearInvBalZero='0' and inv.MonthInvBalZero='0'

                ORDER BY inv.Customer;

       

       

       

       

      //CustomersTotals:

      //LOAD

      //          CMCustomer,

      //          CMCurrency,

      //          Name,

      //          OutstOrdVal,

      //          CreditLimit,

      //          OutstandingTotal,

      //          TermsCode,

      //          DueDays,

      //          InvoicedTotal,

      //          InvoiceNumber,

      //          CMInvoiceDate,

      //          DayDiffrence,

      //          CMSALESPERSON,

      //          CMProductClass,

      //          CMBRANCH,

      //          CMProductClassDescription,

      //          CMQtyInvoiced,

      //          CMNetSalesValue,

      //          StockCode,

      //          TaxStatus,

      //          CurrentInvoice

      //RESIDENT CustomersTotalsTmp;

      //DROP TABLE CustomersTotalsTmp;

       

       

       

       

       

       

      CustomersTotals:

      LOAD

                *

      RESIDENT CustomersTotalsTmp;

      DROP TABLE CustomersTotalsTmp;

        • Re: Resident load problem
          Gysbert Wassenaar

          You're loading all the fields from CustomersTotalsTmp. So the data will be appended to CustomersTotalsTmp unless you specify Noconcatenate. When you drop the table CustomersTotalsTmp everything is gone. Change your last load statement to

           

          CustomersTotals:

          NOCONCATENATE LOAD

                    *

          RESIDENT CustomersTotalsTmp;

          DROP TABLE CustomersTotalsTmp;

           

          Though why you're doing a resident load if you don't change anything is not clear to me. You could leave it out altogether and simply name the first table CustomersTotals instead of CustomersTotalsTmp

            • Re: Resident load problem

              Hi there i am doing a resident load in order to apply the OverDueFlag to the data because naturally DueDays is not contained in the same table as CMInvoiceDate.... Can it be done in any other way that i have no knowledge of???

              Thanks in advance for the quick and efficient responce...

               

               

               

              CustomersTotals:

              NOCONCATENATE LOAD

                        CMCustomer,

                        CMCurrency,

                        Name,

                        OutstOrdVal,

                        CreditLimit,

                        OutstandingTotal,

                        TermsCode,

                        DueDays,

                        InvoicedTotal,

                        InvoiceNumber,

                        CMInvoiceDate,

                        DayDiffrence,

                        CMSALESPERSON,

                        CMProductClass,

                        CMBRANCH,

                        CMProductClassDescription,

                        CMQtyInvoiced,

                        CMNetSalesValue,

                        StockCode,

                        TaxStatus,

                        CurrentInvoice,

                        if(fabs(Interval( CMInvoiceDate - date(today(),'YYYY-MM-DD') , 'dd')) >= DueDays ,1,0) as OverdueFlag

              RESIDENT CustomersTotalsTmp;

              DROP TABLE CustomersTotalsTmp;

                • Re: Resident load problem
                  Gysbert Wassenaar

                  No, that's fine. It's just not what you posted:

                  CustomersTotals:

                  LOAD

                            *

                  RESIDENT CustomersTotalsTmp;

                  DROP TABLE CustomersTotalsTmp;

                   

                  As soon as you add another field then the tables won't be automatically concatenated. So this should work:

                   

                  CustomersTotals:

                  LOAD *,

                  if(fabs(Interval( CMInvoiceDate - date(today(),'YYYY-MM-DD') , 'dd')) >= DueDays ,1,0) as OverdueFlag

                  RESIDENT CustomersTotalsTmp;

                  DROP TABLE CustomersTotalsTmp;

              • Re: Resident load problem
                Miguel Angel Baeyens de Arce

                Hi,

                 

                QlikView is concatenating automatically both tables, as they are identical. Try using the following code:

                 

                CustomersTotals:

                NOCONCATENATE LOAD

                          *

                RESIDENT CustomersTotalsTmp;

                DROP TABLE CustomersTotalsTmp;

                 

                Hope that helps,

                 

                Miguel

                • Re: Resident load problem

                  Hi,

                   

                  Place proper name in load like below   and do same for another tables also.

                   

                   

                  CustomersTotalsTmp:

                  LOAD

                            cust.Customer as CMCustomer,

                            cust.Currency as CMCurrency,

                            cust.Name,

                            cust.OutstOrdVal,

                            cust.CreditLimit,

                            OutstandingTotal,

                            cust.TermsCode,

                            term.DueDays,

                            InvoicedTotal;

                  SQL SELECT

                            cust.Customer,

                            cust.Name,

                            cust.OutstOrdVal as OutstOrdVal,

                            cust.CreditLimit,

                            cust.OutstOrdVal + cbal.CurrentBalance1 as OutstandingTotal,

                            cust.TermsCode,

                            term.DueDays,

                            curr.Currency,

                            cbal.CurrentBalance1 as InvoicedTotal

                            FROM SysproCompanyA.dbo.ArCustomer as cust

                            LEFT JOIN SysproCompanyA.dbo.TblArTerms as term

                            ON cust.TermsCode=term.TermsCode

                            LEFT JOIN SysproCompanyA.dbo.TblCurrency as curr

                            ON cust.Currency=curr.Currency

                            LEFT JOIN SysproCompanyA.dbo.ArCustomerBal as cbal

                            ON cust.Customer=cbal.Customer

                            ORDER BY cust.Customer;

                   

                   

                  I hope it helps you

                   

                  Best of luck