Excluding a calculated value in load script

[#1]

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

[jpapador · Accepted solution]

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

[#3]

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

[jpapador]

Yes.

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

[#5]

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

[#6]

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?

[#7]

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