topic Re: Need help in Expression(Script) in QlikView

Need help in Expression(Script)

Fri, 17 May 2013 05:41:09 GMT

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

Re: Need help in Expression(Script)

kumarnatarajan — Fri, 17 May 2013 05:53:14 GMT

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,

Re: Need help in Expression(Script)

Fri, 17 May 2013 05:56:49 GMT

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,

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

Re: Need help in Expression(Script)

Fri, 17 May 2013 06:16:21 GMT

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

Re: Need help in Expression(Script)

Gysbert_Wassenaar — Sat, 18 May 2013 09:26:37 GMT

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

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