topic Sql query in script for qlikview in QlikView

Sql query in script for qlikview

Thu, 15 Dec 2011 06:47:46 GMT

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.

Sql query in script for qlikview

Thu, 15 Dec 2011 07:09:52 GMT

Hi k2sheann,

do you mean something like this?

LOAD

Sql query in script for qlikview

jagan — Thu, 15 Dec 2011 08:28:54 GMT

Hi,

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

TableName:

LOAD

Sql query in script for qlikview

perumal_41 — Thu, 15 Dec 2011 08:42:03 GMT

please try Script .This script useful for u

TableName:

LOAD

Sql query in script for qlikview

Thu, 15 Dec 2011 09:31:44 GMT

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?

Sql query in script for qlikview

jagan — Thu, 15 Dec 2011 09:37:06 GMT

Hi,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

Re: Sql query in script for qlikview

RickWild64 — Thu, 15 Dec 2011 09:47:41 GMT

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;

Re: Sql query in script for qlikview

RickWild64 — Thu, 15 Dec 2011 10:09:12 GMT

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

Sql query in script for qlikview

jagan — Thu, 15 Dec 2011 12:58:01 GMT

Hi,

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

Regards,
jagan.

Sql query in script for qlikview

RickWild64 — Thu, 15 Dec 2011 15:45:21 GMT

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

Sql query in script for qlikview

Thu, 15 Dec 2011 17:04:36 GMT

Hi Jagan,

I need to get the balance as new field.

I know can done it in set analysis way, but I need to be done in script level. Later I need to do some calculation using the new field.

Set analysis formula cannot apply in script level.