topic Sum if statement used with wildmatch in QlikView

Sum if statement used with wildmatch

Fri, 24 Feb 2012 10:13:39 GMT

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

CELAMBARASAN — Fri, 24 Feb 2012 10:19:12 GMT

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

jagan — Fri, 24 Feb 2012 10:21:19 GMT

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

SunilChauhan — Fri, 24 Feb 2012 10:31:34 GMT

try this

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

Amount)) as Measured_Value

hope this helps

Sum if statement used with wildmatch

Fri, 24 Feb 2012 10:44:13 GMT

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

my code is so far,

LOAD CA,

//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

CELAMBARASAN — Fri, 24 Feb 2012 10:49:05 GMT

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

Fri, 24 Feb 2012 10:56:18 GMT

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