topic Re: Excluding a calculated value in load script in QlikView

Excluding a calculated value in load script

Tue, 25 Feb 2014 22:13:30 GMT

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

Re: Excluding a calculated value in load script

jpapador — Tue, 25 Feb 2014 22:15:18 GMT

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

Re: Excluding a calculated value in load script

Tue, 25 Feb 2014 22:23:07 GMT

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

Re: Excluding a calculated value in load script

jpapador — Tue, 25 Feb 2014 22:27:17 GMT

Yes.

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

Re: Excluding a calculated value in load script

Tue, 25 Feb 2014 22:34:28 GMT

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

Re: Excluding a calculated value in load script

Tue, 25 Feb 2014 22:38:25 GMT

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;

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

But this cannot be done, right?

Re: Excluding a calculated value in load script

Tue, 25 Feb 2014 22:59:32 GMT

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