Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Cumulation in the script with a condition

In the script, I have to calculate a cumulative sum with a condition. The conditions is something like

cumulate column VALUE within each YEAR but for each variant of CONDITION put only the one into the cumulation, that was loaded last (or with the highest ORDER number equivalently)”.

My input and desired output looks like this, INPUT is in Table1. (sorry for the ugly format. I add a PNG with a formatted table picture.

 

INPUT

INPUT

INPUT

INPUT

INPUT

OUTPUT

 

YEAR

ORDER

ITEM

CONDITION

VALUE

CUMULATED

 

2020

1

A

XX

3

3

 

2020

2

B

YY

5

8

 

2020

3

C

ZZ

4

12

 

2021

1

A

XX

3

3

 

2021

2

B

YY

5

8

 

2021

3

C

ZZ

4

12

 

2021

4

D

UU

7

19

 

2021

5

E

TT

5

24

 

2022

1

C

ZZ

4

4

 

2022

2

D

UU

7

11

 

2022

3

E

TT

5

16

 

2022

4

F

UU

8

17

16+8-7

2022

5

G

ZZ

5

18

17+5-4

2022

6

H

TT

9

22

18+9-5

2022

7

J

UU

9

23

22+9-8

 

 

 

After sorting the input Table1 by YEAR and ORDER, I used a cumulation with peek. That worked but did not consider the condition.

Table2:
Load
YEAR,
ORDER,
ITEM,
CONDITION,
VALUE,

if(YEAR =Previous(YEAR) ,
//then
   VALUE+peek(VALUE_cumulative),
//else
   VALUE
   )
as VALUE_cumulative

Resident Table1;

 

How can I apply the condition as reflected in the last 4 rows of the table above?

Labels (4)
1 Solution

Accepted Solutions
Highlighted
Master
Master

Hi,

You can optimize the code. I left it long enough to explain the approach.

But it's a nice challenge, thank you.

Data:

LOAD (YEAR&'_'&CONDITION&'_'&ORDER) as autoID,* inline [
    YEAR, ORDER, ITEM, CONDITION, VALUE
    2020, 1, A, XX, 3
    2020, 2, B, YY, 5
    2020, 3, C, ZZ, 4
    2021, 1, A, XX, 3
    2021, 2, B, YY, 5
    2021, 3, C, ZZ, 4
    2021, 4, D, UU, 7
    2021, 5, E, TT, 5
    2022, 1, C, ZZ, 4
    2022, 2, D, UU, 7
    2022, 3, E, TT, 5
    2022, 4, F, UU, 8
    2022, 5, G, ZZ, 5
    2022, 6, H, TT, 9
    2022, 7, J, UU, 9
];


t:
noconcatenate
load * resident Data order by autoID;


Table2:
noconcatenate
Load *,

if(CONDITION =Previous(CONDITION) ,VALUE-peek(VALUE),VALUE) as VALUE_

Resident t;
drop table t;

left join load * resident Data;
drop table Data;


tempoutput:
noconcatenate
load rowno() as ID,* resident Table2 order by YEAR,ORDER;

drop table Table2;

output:

noconcatenate

Load *,

if(YEAR =Previous(YEAR) ,VALUE_+peek(VALUE_cumulative),VALUE_) as VALUE_cumulative

Resident tempoutput;

drop table tempoutput;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

8 Replies
Highlighted
Master
Master

Hi,

You can optimize the code. I left it long enough to explain the approach.

But it's a nice challenge, thank you.

Data:

LOAD (YEAR&'_'&CONDITION&'_'&ORDER) as autoID,* inline [
    YEAR, ORDER, ITEM, CONDITION, VALUE
    2020, 1, A, XX, 3
    2020, 2, B, YY, 5
    2020, 3, C, ZZ, 4
    2021, 1, A, XX, 3
    2021, 2, B, YY, 5
    2021, 3, C, ZZ, 4
    2021, 4, D, UU, 7
    2021, 5, E, TT, 5
    2022, 1, C, ZZ, 4
    2022, 2, D, UU, 7
    2022, 3, E, TT, 5
    2022, 4, F, UU, 8
    2022, 5, G, ZZ, 5
    2022, 6, H, TT, 9
    2022, 7, J, UU, 9
];


t:
noconcatenate
load * resident Data order by autoID;


Table2:
noconcatenate
Load *,

if(CONDITION =Previous(CONDITION) ,VALUE-peek(VALUE),VALUE) as VALUE_

Resident t;
drop table t;

left join load * resident Data;
drop table Data;


tempoutput:
noconcatenate
load rowno() as ID,* resident Table2 order by YEAR,ORDER;

drop table Table2;

output:

noconcatenate

Load *,

if(YEAR =Previous(YEAR) ,VALUE_+peek(VALUE_cumulative),VALUE_) as VALUE_cumulative

Resident tempoutput;

drop table tempoutput;

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Creator II
Creator II

Thank you so much !!!

The solution is really nice and with the script copied easy to understand. You made my day 🙂

Highlighted
Creator II
Creator II

PS: 

One open question... What do we need the rowNo() as ID for in the final table? Is it just to control the sequence of rows visually?

Highlighted
Master
Master

Yes 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Contributor
Contributor

In my opinion your solution doesn't care the case where the condition remain the same exactly when the year change !

Highlighted
Creator II
Creator II

We look at the years independently. So wether a condition changes or not is not relevant. But thanks for rethinking.
Highlighted
Contributor
Contributor

Hy Michael_Klix,

In my opinion the problem is this instruction:

if(CONDITION = Previous(CONDITION) ,VALUE-peek(VALUE),VALUE) as VALUE_

Actually the value of field VALUE_, becomes VALUE-peek(VALUE) if CONDITION = Previous(CONDITION), even if the year changes too.

if the condition remain the same when the year changes, the value of field VALUE_ should be VALUE and not VALUE-peek(VALUE) because as you said, the years are independent !

Highlighted
Creator II
Creator II

Fair point. It is sorted first by year and within each year by condition. But there might be a case that the last condition in a year is the same as in the following year. We should avoid that it calculates an increment in this case. Actually practically this wont happen. But it is a weak spot that should be covered.
Solution would be an if() that only calculates within a year.
Thanks !