Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excluding a calculated value in load script

Hi guys,

I want to exclude the records where COLLECTIONDAYS or (date(CLOSED)-date(TRANSDATE)) = 0

How can I do that in the load script?

Or if not possible in the load script.  How can I exclude these values in a chart expression using the AVG formula?

LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    
date(CLOSED) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
    
date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' AND CLOSED > '01.01.1900' and not AMOUNTCUR = '0' ;

1 Solution

Accepted Solutions
Partner
Partner

LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    
date(CLOSED) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
    
date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS

WHERE (date(CLOSED)-date(TRANSDATE)) > 0;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' AND CLOSED > '01.01.1900' and not AMOUNTCUR = '0' ;

View solution in original post

6 Replies
Partner
Partner

LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    
date(CLOSED) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
    
date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS

WHERE (date(CLOSED)-date(TRANSDATE)) > 0;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' AND CLOSED > '01.01.1900' and not AMOUNTCUR = '0' ;

View solution in original post

Not applicable

Wow, that was a quick reply, and fixed my problem which I have been trying to solve for an hour

Thanks so much.

Can I ask one more question?

I have a field called: date(CLOSED) as PAYMENTDATE,

This field has the value of 01.01.1900 if an invoice has not been paid.  If it has been paid, it has the date of payment.  Is there a way that I can overwrite 01.01.1900 with todays date?

LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
month(TRANSDATE) as MONTH,
    
year(TRANSDATE) as YEAR,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    
date(CLOSED) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
    
date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS WHERE (date(CLOSED)-date(TRANSDATE)) > 0;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' AND CLOSED > '01.01.1900' and not AMOUNTCUR = '0' ;

Partner
Partner

Yes.

If(Date(Closed) = '01.01.1900', Today(), Date(Closed)) as PAYMENTDATE

Not applicable

Like this?  It does not seem to be working.  I do not get any PAYMENTDATE with either 01.01.1900 or 25.02.2014


LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
month(TRANSDATE) as MONTH,
    
year(TRANSDATE) as YEAR,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    
CLOSED,
    
If(Date(CLOSED) = '01.01.1900', Today(), Date(CLOSED)) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
    
date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS WHERE (date(CLOSED)-date(TRANSDATE)) > 0;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' and not AMOUNTCUR = '0' ;

Not applicable

Oh, and if I change CLOSED = 01.01.1900 to today() then I would need to change:


date(CLOSED)-date(TRANSDATE) as COLLECTIONDAYS WHERE (date(CLOSED)-date(TRANSDATE)) > 0;


to

date(PAYMENTDATE)-date(TRANSDATE) as COLLECTIONDAYS WHERE (date(PAYMENTDATE)-date(TRANSDATE)) > 0;


But this cannot be done, right?


Not applicable

Seems to be ok, like this.  Does it look ok to you?

LOAD DATAAREAID&ACCOUNTNUM as CUSTACCOUNTNUM,
    
date(TRANSDATE) as INVOICEDATE,
    
month(TRANSDATE) as MONTH,
    
year(TRANSDATE) as YEAR,
    
DATAAREAID&VOUCHER as VOUCHERNUM,
    
INVOICE as INVOICENUMBER,
    
AMOUNTCUR,
    
SETTLEAMOUNTCUR,
    
AMOUNTMST,
    
SETTLEAMOUNTMST,
    
CURRENCYCODE,
    
DUEDATE,
    
LASTSETTLEVOUCHER,
    
LASTSETTLEDATE,
    
LASTEXCHADJVOUCHER,
    If(Date(CLOSED) = '01.01.1900', Today(), Date(CLOSED)) as PAYMENTDATE,
    
TRANSTYPE,
    
SETTLEMENT,
     (
If(Date(CLOSED) = '01.01.1900', Today(), Date(CLOSED)))-date(TRANSDATE) as COLLECTIONDAYS WHERE (If(Date(CLOSED) = '01.01.1900', Today(), Date(CLOSED)))-date(TRANSDATE) > 0;
SQL SELECT *
FROM "Dynamics_2009".dbo.CUSTTRANS where TRANSTYPE = '6' and not AMOUNTCUR = '0' ;