Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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' ;
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' ;
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' ;
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' ;
Yes.
If(Date(Closed) = '01.01.1900', Today(), Date(Closed)) as PAYMENTDATE
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' ;
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?
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' ;