Resident load problem

[#1]

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;

[Gysbert_Wassenaar · Accepted solution]

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. 

[Miguel_Angel_Baeyens]

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

[#4]

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

[#5]

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;

[Gysbert_Wassenaar · Accepted solution]

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;