Need help in Expression(Script)

[#1]

Hi,

Need help in Orange colored part, how to do this sql part.

CurrentData:

SQL

SELECT

    CURDATE() as Date1,

    Customer_id,

    Customer_Sub,

    Customer_Location,

    Invoice_No,

    Invoice_Amount,

     If(GETDATE() - [DueDate] >= 30,Count(Invoice_No) as NbOverdueInv,

        If(GETDATE() - [DueDate] >= 30,Sum(Invoice_Amount) as NbOverdueInv,

    Count(Invoice_No) as NumberofInvoices,

    Sum(Invoice_Amount) as TotalAmt

FROM Master.dbo Group by Customer_id,Customer_Sub,Customer_Location, Invoice_No,Invoice_Amount;

STORE CurrentData into CurrentData.qvd(qvd);

Regards,

Chriss

[kumarnatarajan]

Hi,

Close your if condition brackets. and Field name must be unique.

Else

use below mentioned code.

Ex:

if(DATEDIFF(day,Now(),[DueDate])>= 30,Count(Invoice_No)) as NbOverdueInvCount,

if(DATEDIFF(day,Now(),[DueDate])>= 30,Sum(Invoice_Amount)) as NbOverdueInvSum,

[#3]

TEST:

Load *,

If(GETDATE() - [DueDate] >= 30,Count(Invoice_No) as NbOverdueInv,

        If(GETDATE() - [DueDate] >= 30,Sum(Invoice_Amount) as NbOverdueInv;

SQL

SELECT *

FROM Master.dbo

CurrentData:

load

Customer_id,

Customer_Sub,

Customer_Location,

Invoice_No,

Invoice_Amount,

NbOverdueInv,

NbOverdueInv,

Count(Invoice_No) as NumberofInvoices,

    Sum(Invoice_Amount) as TotalAmt

FROM TEST Group by Customer_id,Customer_Sub,Customer_Location, Invoice_No,Invoice_Amount,NbOverdueInv,NbOverdueInv;

STORE CurrentData into CurrentData.qvd(qvd);

[#4]

Hi Kumar,

Thanks for your reply, as I am getting the error of "Incorrect syntax near the keyword 'if'."

In the SQL select is not possible to use the IF conditon.

Regards

Chriss

[Gysbert_Wassenaar]

CASE WHEN GETDATE() - [DueDate] >= 30 THEN Count(Invoice_No) END as NbOverdueInv,

CASE WHEN GETDATE() - [DueDate] >= 30 THEN Sum(Invoice_Amount) END as NbOverdueInv,