topic Re: get Aggr Sum per each row in QlikView

get Aggr Sum per each row

maxsheva — Sat, 21 Oct 2017 10:24:49 GMT

Hi,

data:

LOAD * Inline [

Group, Subgroup, Date, Value, Value2

Group1, Subgroup1, 01/01/2017, 100, 2

Group1, Subgroup1, 01/02/2017, 0, 3

Group1, Subgroup1, 01/03/2017, 200, 2

Group1, Subgroup1, 01/04/2017, 0, 1

Group2, Subgroup1, 01/05/2017, 100, 4

Group2, Subgroup1, 01/06/2017, 0, 2

Group2, Subgroup1, 01/07/2017, 0, 1

]

I need to get the result as in table2. But I would like to show some logic in table1.

Any suggestion is welcome.

table1

Group	Subgroup	Date	Value	RepeatValue	Value2	Multiply( Sum(Value2)*RepeatValue )
Group1	Subgroup1	01/01/2017	100	100	2	200
Group1	Subgroup1	01/02/2017	0	100	3	300
Group1	Subgroup1	01/0/3/2017	200	200	2	400
Group1	Subgroup1	01/04/2017	0	200	1	200
Group2	Subgroup1	01/05/2017	100	100	4	400
Group2	Subgroup1	01/06/2017	0	100	2	200
Group2	Subgroup1	01/07/2017	0	100	1	100

table2

Group	Sum(Multiply)
Group1	1100
Group2	700

Re: get Aggr Sum per each row

swuehl — Sat, 21 Oct 2017 10:52:40 GMT

Create the RepeatValue field in the script:

SET DateFormat = 'MM/DD/YYYY';

INPUT:

LOAD * Inline [

Group, Subgroup, Date, Value, Value2

Group1, Subgroup1, 01/01/2017, 100, 2

Group1, Subgroup1, 01/02/2017, 0, 3

Group1, Subgroup1, 01/03/2017, 200, 2

Group1, Subgroup1, 01/04/2017, 0, 1

Group2, Subgroup1, 01/05/2017, 100, 4

Group2, Subgroup1, 01/06/2017, 0, 2

Group2, Subgroup1, 01/07/2017, 0, 1

];

RESULT:

LOAD *,

If(Value = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue),Value) as RepeatValue

Resident INPUT

ORDER BY Group, Subgroup, Date asc;

DROP TABLE INPUT;

Then use the field in your chart expression

Group	*Sum(RepeatValueValue2)**
	1800
Group1	1100
Group2	700

Re: get Aggr Sum per each row

maxsheva — Sat, 21 Oct 2017 15:27:27 GMT

Stefan, thanks for solution.

Would you please advise how to make the same in case where two different tables?

Script below doesn't provide an expected result.

INPUT1:
LOAD * Inline [
Group, Subgroup, Date, Value
Group1, Subgroup1, 01/01/2017, 2
Group1, Subgroup1, 01/02/2017, 3
Group1, Subgroup1, 01/03/2017, 2
Group1, Subgroup1, 01/04/2017, 1
Group2, Subgroup1, 01/05/2017, 4
Group2, Subgroup1, 01/06/2017, 2
Group2, Subgroup1, 01/07/2017, 1
];

Left Join(INPUT1)
INPUT2:
LOAD * Inline [
Group, Subgroup, Date, Value2
Group1, Subgroup1, 01/01/2017, 100

Group1, Subgroup1, 01/03/2017, 200
Group2, Subgroup1, 01/05/2017, 100
];

RESULT:
LOAD *,
If(Value2 = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2
Resident INPUT1
ORDER BY Group, Subgroup, Date asc;

DROP TABLE INPUT1;

Re: get Aggr Sum per each row

swuehl — Sat, 21 Oct 2017 15:37:18 GMT

When checking if the value of Value2 is a valid one, you need to consider that due to the JOIN, you need to check for NULL, not zero.

RESULT:

LOAD *,

If(Len(trim(Value2))=0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2

Resident INPUT1

ORDER BY Group, Subgroup, Date asc;

Re: get Aggr Sum per each row

maxsheva — Sat, 21 Oct 2017 21:04:24 GMT

It works. Thank you very much!