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

Sql query in script for qlikview

Hello,

May I know to apply in script level?

SUM(AMT) for CATEGORY in ('123', '456', '789')

I want to apply in script not in chart .

Thanks.

1 Solution

Accepted Solutions
RickWild64
Partner - Creator
Partner - Creator

Hi k2shean, You can use something like the script below. I have loaded your table inline just for test purposes. Of course you would really load it from its proper source. This solution gives one row in the Agg table. If you have higher dimensions, use Group By as jagan mohan suggests. In this case you need to Load all the fields you group by.

Dets:

load * inline [

ID,CATEGORY,AMT

1,123,34

2,456,29

3,789,10

4,abc,4

5,def,55

]

;

Agg:

load

   sum(if(match( CATEGORY , '123' , '456' ,'789')>0, AMT,0)) - sum(if(match( CATEGORY , 'abc' , 'def')>0, AMT,0)) as AggrAmt

resident Dets;

View solution in original post

10 Replies
Not applicable
Author

Hi k2sheann,

do you mean something like this?

LOAD

      CATEGORY

     ,SUM(AMT) AS SUM_AMOUNT

FROM <Table>

GROUP BY CATEGORY

Regards,

Reyman

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this (<TableName> replace this with your original table name)

TableName:

LOAD

      CATEGORY

     ,SUM(AMT) AS SUM_AMOUNT

FROM <TableName>

WHERE CATEGORY = '123' OR CATEGORY = '456' OR CATEGORY ='789'

GROUP BY CATEGORY;

Regards,

Jagan.

perumal_41
Partner - Specialist II
Partner - Specialist II

please try Script .This script useful for u

TableName:

LOAD

      CATEGORY

     ,SUM(AMT) AS SUM_AMOUNT

FROM <TableName>

where match( CATEGORY , '123' , '456' ,'789')>0

GROUP BY CATEGORY;

Regards ,

Perumal.A

Not applicable
Author

Hi,

Thanks. My table is like:

ID,CATEGORY,AMT

1,123,34

2,456,29

3,789,10

4,abc,4

5,def,55

If I want to:

Sum(AMT) for CATEGORY in ('123', '456', '789') - Sum(AMT) for CATEGORY in ('abc', 'def')

How to do it in script level?

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expression

=Sum({<CATEGORY={'123', '456', '789'} >} AMT) -Sum({<CATEGORY={'abc', 'def}>} AMT)

Hope this helps you.

Regards,

Jagan.

RickWild64
Partner - Creator
Partner - Creator

Hi k2shean, You can use something like the script below. I have loaded your table inline just for test purposes. Of course you would really load it from its proper source. This solution gives one row in the Agg table. If you have higher dimensions, use Group By as jagan mohan suggests. In this case you need to Load all the fields you group by.

Dets:

load * inline [

ID,CATEGORY,AMT

1,123,34

2,456,29

3,789,10

4,abc,4

5,def,55

]

;

Agg:

load

   sum(if(match( CATEGORY , '123' , '456' ,'789')>0, AMT,0)) - sum(if(match( CATEGORY , 'abc' , 'def')>0, AMT,0)) as AggrAmt

resident Dets;

RickWild64
Partner - Creator
Partner - Creator

Hi Jagan,

I think you need to use the conditional form if you want to do the calculation in the script. Your set analysis version works nicely in an object, but = 0 when used in the script.

Regards,

Rick

jagan
Luminary Alumni
Luminary Alumni

Hi,

May I know the reason, why you are calculating this value in Script.

Regards,
jagan.

RickWild64
Partner - Creator
Partner - Creator

Hi Jagan,

In this case, because that's what the OP asked. In general, for many calculations it's better to do them in the script if you can. Then QlikView only has to do the calculation once, and you only have to maintain one version of the formula.

Regards,

Rick