Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum and where clause to Qlikview

Hello,

I have this code I want to convert to Qlikview:

 

sum(GRANTED_FACILITY_AMT) CELL_VALUE
from CRI_NCA_AS..FACT_APPLICATIONS_JUN2015
where PRODUCT_CODE='MLOAN'
and REG_LAST_3MONTHS_IND='Y'
group by case MLOANS_BANDS_CODE

Please assists,

Mbini

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

You can try this:

sum(

     if(

          PRODUCT_CODE='MLOAN'

               and

          REG_LAST_3MONTHS_IND='Y'

               and

          match(MLOANS_BANDS_CODE,'A','B','C','D','E','F')


     ,FINAL_ML_AMT

)

 

View solution in original post

11 Replies
Not applicable
Author

Please try like below:

LOAD

MLOANS_BANDS_CODE,    

sum(GRANTED_FACILITY_AMT) CELL_VALUE
from CRI_NCA_AS..FACT_APPLICATIONS_JUN2015.qvd (qvd)
where Match(PRODUCT_CODE,'MLOAN')
and Match(REG_LAST_3MONTHS_IND,'Y')
group by case MLOANS_BANDS_CODE

;

Not applicable
Author

Hi,

But i want to put this code on the front end

Not applicable
Author

I have tried this code:

if(match(PRODUCT_CODE,'MLOAN') and match(REG_LAST_3MONTHS_IND,'Y') and match(MLOANS_BANDS_CODE,'A','B','C','D','E','F'),sum(FINAL_ML_AMT))

Not applicable
Author

Let us assume you have all these 4 fields on data model, you can do with set analysis.

Create Straight table or other required chart with Dimension & Expressions.

Dimension: MLOANS_BANDS_CODE

Expression: sum({<PRODUCT_CODE={"MLOAN"},REG_LAST_3MONTHS_IND={"Y"}>}GRANTED_FACILITY_AMT)

If you want show total value on the Text Object simply use the above expression on text object.

Not applicable
Author

on which object you are trying the expression ?

JonnyPoole
Employee
Employee

You can try this:

sum(

     if(

          PRODUCT_CODE='MLOAN'

               and

          REG_LAST_3MONTHS_IND='Y'

               and

          match(MLOANS_BANDS_CODE,'A','B','C','D','E','F')


     ,FINAL_ML_AMT

)

 

hic
Former Employee
Former Employee

Make a chart (bar chart, pivot table, or what you want) and use the following:

Dimension: MLOANS_BANDS_CODE

Expression: Sum({$<PRODUCT_CODE={'MLOAN'}, REG_LAST_3MONTHS_IND={'Y'}>}

   GRANTED_FACILITY_AMT)

HIC

Not applicable
Author

it complains about the syntax of this code!!

Not applicable
Author

It complains about the syntax on the code