Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

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


jagan
Luminary Alumni
Luminary Alumni

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.


SunilChauhan
Champion
Champion

try this

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

Amount)) as   Measured_Value

hope this helps

Sunil Chauhan
Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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