Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
Hi k2sheann,
do you mean something like this?
LOAD
CATEGORY
,SUM(AMT) AS SUM_AMOUNT
FROM <Table>
GROUP BY CATEGORY
Regards,
Reyman
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.
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
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?
Hi,
Try this expression
=Sum({<CATEGORY={'123', '456', '789'} >} AMT) -Sum({<CATEGORY={'abc', 'def}>} AMT)
Hope this helps you.
Regards,
Jagan.
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;
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
Hi,
May I know the reason, why you are calculating this value in Script.
Regards,
jagan.
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