topic Subtraction of 2 rows depending a 3rd row (Script) in QlikView

Subtraction of 2 rows depending a 3rd row (Script)

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

Hi community,

I have something like this.

Product TypeProd A B

1               7145     1000   0
2               7289     0          2000
3               4888     2500    0
4               7265     3200    0
5               1477     0          500
6               3655     1000    0
7               1788     1500    0

So, i want sum(A) - Sum(B) but only when TypeProd start with 71 or 72 and i want to do this in script. Any ideas?

Thanks in advance

Best Regards

Bruno Paulo

Re: Subtraction of 2 rows depeding a 3rd row (Script)

sunny_talwar — Thu, 12 Oct 2017 17:12:21 GMT

May be this

LOAD Product,

TypeProd,

If(WildMatch(TypeProd, '71*', '72*'), A-B) as NewColumn

From ....

Re: Subtraction of 2 rows depending a 3rd row (Script)

brunopaulo — Thu, 12 Oct 2017 17:16:46 GMT

I have Time (Year and Month) as dimension it appear some results without time associated and negative values. (it shouldnt happen).

Any other idea?

Re: Subtraction of 2 rows depeding a 3rd row (Script)

vkish16161 — Thu, 12 Oct 2017 17:20:18 GMT

If(WildMatch(TypeProd, "71*", "72*"), A-B) as NewColumn

- Double quotes should do the trick. Let us know if it doesn't work.

Re: Subtraction of 2 rows depending a 3rd row (Script)

brunopaulo — Thu, 12 Oct 2017 17:22:52 GMT

I guess Sunny Solutions is almost rigth in terms of values, but it creats 1 bar without time associated and i can't get it why. The double quotes didn't work.

Re: Subtraction of 2 rows depending a 3rd row (Script)

sunny_talwar — Thu, 12 Oct 2017 17:25:01 GMT

Without time? Where is Time coming from? May you need to check 'Suppress Zero Value' on presentation tab?

Re: Subtraction of 2 rows depending a 3rd row (Script)

brunopaulo — Fri, 13 Oct 2017 08:52:13 GMT

Yes, supress Zero Value solved the issue. Thank you Sunny, perfect as always. Regards