topic Re: Addition or subtraction of values in rows in QlikView

Addition or subtraction of values in rows

Anonymous — Wed, 12 Jun 2019 10:58:32 GMT

Hello Experts,

i want to add and subtract the values received in load statement as shown in below table.

i have all data available in load statement except the calculated values marked in Yellow.

Please note that each value is received in a separate load statement and joined together.

Is it possible to do this?

For your information the number of rows are fixed.

thanks in Advance

Re: Addition or subtraction of values in rows

sunny_talwar — Wed, 12 Jun 2019 11:52:19 GMT

Do you have 'Total X+Y' and 'B-A' and 'T+G-Y' as a value within GROUP?

Re: Addition or subtraction of values in rows

Anonymous — Wed, 12 Jun 2019 11:56:20 GMT

Not really 😞

I have X, Y separate as values but not 'Total X +Y'... but i want the result in table like that.

All these values coming from a single QVD based on different conditions. Each with a separate load.

Each Group has a different condition.

Re: Addition or subtraction of values in rows

sunny_talwar — Fri, 14 Jun 2019 10:58:34 GMT

Would you be able to share a small sample to show what you mean?

Re: Addition or subtraction of values in rows

Anonymous — Fri, 14 Jun 2019 02:52:17 GMT

Hello Sunny,

Sorry, was not in office yesterday.

Please see attached example. I have written few load statements for example.

i have all load statements for all criteria carried out on same table.

the expected result is like

Thanks

Re: Addition or subtraction of values in rows

Channa — Fri, 14 Jun 2019 03:04:55 GMT

do with wildMatch

Concatenate below with your previous load

'X+Y',IF(wildMatch(Group,'X','Y'),SUm(Value)) resident Table

'A-B',IF(wildMatch(Group,'A',),SUm(Value))-IF(wildMatch(Group,'B',),SUm(Value))

Re: Addition or subtraction of values in rows

Anonymous — Fri, 14 Jun 2019 03:17:27 GMT

Sorry, but it's not working... gives error as invalid expression

SUMX_Y:
LOAD
'X+Y' as SGroup,
IF(wildMatch(Desc,'X*','Y*'),SUM(Value),0) as StockValue

resident Master;

Re: Addition or subtraction of values in rows

Anonymous — Fri, 14 Jun 2019 03:25:56 GMT

It needs a Group by for sum 😞

Re: Addition or subtraction of values in rows

Channa — Fri, 14 Jun 2019 03:57:50 GMT

Table1:
LOAD
"Desc",
Value
FROM [lib://Source/Grouping.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD
'X+Y' as Desc,
sum(Value) as Value Resident Table1 where WildMatch(Desc,'X','Y') ;

try

Re: Addition or subtraction of values in rows

Anonymous — Fri, 14 Jun 2019 04:07:10 GMT

Thanks, it already works for sum but how do i manage subtraction for
B-A
T+G-H

Re: Addition or subtraction of values in rows

Anonymous — Fri, 14 Jun 2019 04:21:49 GMT

Hello,

i managed it to do using a variable as below.

but i was wondering if i can avoid one more resident load and instead hust load 2 values from 2 variables in load statement like . It doesn't give any error but no values in the table.

SUMB1:
LOAD
'B-A' as SGroup,
($(vB) - $(vA))as StockValue;

Re: Addition or subtraction of values in rows

Channa — Fri, 14 Jun 2019 04:53:46 GMT

can you try preceding load

Load
'A-B' as Desc,(ValueB-ValueA) as Value ;

LOAD
IF(Desc='B', Value) as ValueB ,
IF(Desc='A', Value) as ValueA
FROM [lib://Source/Grouping.xlsx]
(ooxml, embedded labels, table is Sheet1) where WildMatch(Desc,'B','A') ;

Re: Addition or subtraction of values in rows

sunny_talwar — Fri, 14 Jun 2019 11:05:29 GMT

Check attached

Re: Addition or subtraction of values in rows

Anonymous — Tue, 18 Jun 2019 04:42:36 GMT

Thanks Genius 🙂