topic Re: Resident load problem in QlikView

Resident load problem

Tue, 26 Mar 2013 13:41:30 GMT

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 — Tue, 26 Mar 2013 13:46:30 GMT

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

Miguel_Angel_Baeyens — Tue, 26 Mar 2013 13:47:33 GMT

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

Tue, 26 Mar 2013 13:50:47 GMT

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

Re: Resident load problem

Tue, 26 Mar 2013 13:56:45 GMT

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 — Tue, 26 Mar 2013 14:03:25 GMT

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;