Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
rickwild
Contributor

Re: Sql query in script for qlikview

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;

10 Replies
Not applicable

Sql query in script for qlikview

Hi k2sheann,

do you mean something like this?

LOAD

      CATEGORY

     ,SUM(AMT) AS SUM_AMOUNT

FROM <Table>

GROUP BY CATEGORY

Regards,

Reyman

MVP & Luminary
MVP & Luminary

Sql query in script for qlikview

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
Valued Contributor II

Sql query in script for qlikview

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

Sql query in script for qlikview

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?

MVP & Luminary
MVP & Luminary

Sql query in script for qlikview

Hi,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

rickwild
Contributor

Re: Sql query in script for qlikview

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;

rickwild
Contributor

Re: Sql query in script for qlikview

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

MVP & Luminary
MVP & Luminary

Sql query in script for qlikview

Hi,

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

Regards,
jagan.

rickwild
Contributor

Sql query in script for qlikview

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