Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Shrikant_Qlik
New Contributor III

Addition or subtraction of values in rows

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?

2019-06-12_16h03_07.png

 

For your information the number of rows are fixed.



thanks in Advance

2 Solutions

Accepted Solutions

Re: Addition or subtraction of values in rows

Check attached

image.png

Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

Thanks Genius 🙂
13 Replies

Re: Addition or subtraction of values in rows

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

Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

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

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

Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

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

2019-06-14_08h21_34.png

Thanks

 

Channa
Valued Contributor III

Re: Addition or subtraction of values in rows

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))

Channa
Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

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;

Highlighted
Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

It needs a Group by for sum 😞
Channa
Valued Contributor III

Re: Addition or subtraction of values in rows

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

Channa
Shrikant_Qlik
New Contributor III

Re: Addition or subtraction of values in rows

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