6 Replies Latest reply: Feb 24, 2012 5:56 AM by mrpjspencer

# Sum if statement used with wildmatch

Good morning, why doesnt the following statement work?

Qlickview politely tells me  'Garbage after statement!' Programming Euphamisms!

Sum(Amount)(if(wildmatch(Rate_Category, 'M*'))) as Measured_Value

Thanks.

• ###### Re: Sum if statement used with wildmatch

Hi,

Expression should be

if(wildmatch(Rate_Category, 'M*'),Sum(Amount)) as Measured_Value

Did you used group by clause here?because sum() needs group by.

I think it should be group by Rate_Category.

Celambarasan

• ###### Re: Sum if statement used with wildmatch

Hi,

Is Sum(Amount) and

(if(wildmatch(Rate_Category, 'M*'))) as Measured_Value

are separate fields?

If they are separate fields then use

Sum(Amount) AS TotalAmount,

(if(wildmatch(Rate_Category, 'M*'))) as Measured_Value

'

'

'

Can you attach the full script.

Regards,

Jagan.

• ###### Sum if statement used with wildmatch

try this

Sum(if(wildmatch(Rate_Category, 'M*'),

Amount)) as   Measured_Value

hope this helps

• ###### Sum if statement used with wildmatch

tried all of the suggestions so far, says invalid expression.

my code is so far,

//Amount,

if(today()-Due_Date>=0 and Today()-Due_Date<=365,'0-1 Years',

if(today()-Due_Date>=366 and Today()-Due_Date<=730,'1-2 Years',

if(today()-Due_Date>=731 and Today()-Due_Date<=1096,'2-3 Years',

if(today()-Due_Date>=1097 and Today()-Due_Date<=1461,'3-4 Years',

if(today()-Due_Date>=1462,'4+ Years'))))) as Years,

if(wildmatch(Rate_Category, 'M*'),Sum(Amount)) as Measured_Value

• ###### Sum if statement used with wildmatch

Hi,

I think there should be simply amount instead of sum(amount).

if(today()-Due_Date>=0 and Today()-Due_Date<=365,'0-1 Years',

if(today()-Due_Date>=366 and Today()-Due_Date<=730,'1-2 Years',

if(today()-Due_Date>=731 and Today()-Due_Date<=1096,'2-3 Years',

if(today()-Due_Date>=1097 and Today()-Due_Date<=1461,'3-4 Years',

if(today()-Due_Date>=1462,'4+ Years'))))) as Years,

if(wildmatch(Rate_Category, 'M*'),Amount) as Measured_Value

Also check whether you have Rate_Category field there?

Celambarasan

• ###### Sum if statement used with wildmatch

Ive sorted it!

if(wildmatch(Rate_Category, 'M*'),Sum(Amount)) as Measured_Value

my problem is that I was trying to use the same wildmatch arguement in the same table, ive subsequently discovered that 'Rate Category' exists in two different tables. ive moved it and got it working