Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chuckklaniecki
Contributor II
Contributor II

New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

Greetings to Qlik Cognoscenti.

I have a question, and if anyone could shed light, I would appreciate it deeply.  I'm a relative Qlik noob, and I thus apologize if my inquiry is overly simple.


The topic has to do with summing a field under certain conditions.  I'd STRONGLY prefer to accomplish this in the data load rather than an active Qlik expression, for I fear the latter may hamper performance.

Consider the following inline data load:

TestData:

LOAD * INLINE [

    RecNum, Contract_Num, Period, Amount, ForwardCumulAmt

    1,    Contract001, 201801, 11, 15

    2,    Contract001, 201802, -3, 4

    3,    Contract001, 201803, 6, 7

    4,    Contract001, 201804, 1, 1

    5,    Contract002, 201801, 77, 106

    6,    Contract002, 201802, 14, 29

    7,    Contract002, 201803, -6, 15

    8,    Contract002, 201803, 20, 15

    9,    Contract002, 201804, 1, 1

];

The goal here is to have the Qlik data load calculate the field ForwardCumulAmt.  I provided that field for these nine sample rows merely to help explain the question. When in production, that data element will not be available.  Rather, it must be calculated.  Following is how the field is calculated for just a few rows:

For RecNum 1:  Sum (11, -3, 6, 1) = 15

For RecNum 2:  Sum (-3, 6, 1) = 4


Algorithm in words:  Sum across Amount field where:

          (A) Contract_Num is the same as Contract_Num for current record

          AND

          (B) Period >= Period for current record


Two more examples:

For RecNum 7: Sum (-6, 20, 1) = 15

For RecNum 8: Sum (-6, 20, 1) = 15

NB:  Please note that field Period for RecNum 7 and RecNum 8 happens to be the same (i.e., 201803).  This is not an error on my part.  It is a necessary characteristic of the data.


Many thanks in advance.


Cheers!


Regards,

Chuck

1 Solution

Accepted Solutions
undergrinder
Specialist II
Specialist II

Hi Charles,

Try this:

TestData:

LOAD * INLINE [

RecNum,Contract_Num,Period,Amount,ForwardCumulAmt

1,Contract001,201801,11,15

2,Contract001,201802,-3,4

3,Contract001,201803,6,7

4,Contract001,201804,1,1

5,Contract002,201801,77,106

6,Contract002,201802,14,29

7,Contract002,201803,-6,15

8,Contract002,201803,20,15

9,Contract002,201804,1,1

] (delimiter is ',');


Final:

Load

RecNum,

    Contract_Num,

    Period,

    Amount,

    ForwardCumulAmt as data_test

Resident TestData

order by Contract_Num, Period desc;


tmp:

Load

    Contract_Num,

    Period,

    sum(Amount) as Amount_period

Resident TestData

Group by Contract_Num,

        Period

order by Contract_Num,

        Period desc;


drop table TestData;


Left join(Final)

Load Contract_Num,

    Period,

RangeSum(Amount_period, if(Peek('Contract_Num')=Contract_Num,Peek('ForwardCumulAmt'))) as ForwardCumulAmt

Resident tmp;


drop table tmp;

qcf is attached.

I didn't drop the original ForwardCumulAmt field (rename to data_test) for testing purpose.

G.

View solution in original post

3 Replies
undergrinder
Specialist II
Specialist II

Hi Charles,

Try this:

TestData:

LOAD * INLINE [

RecNum,Contract_Num,Period,Amount,ForwardCumulAmt

1,Contract001,201801,11,15

2,Contract001,201802,-3,4

3,Contract001,201803,6,7

4,Contract001,201804,1,1

5,Contract002,201801,77,106

6,Contract002,201802,14,29

7,Contract002,201803,-6,15

8,Contract002,201803,20,15

9,Contract002,201804,1,1

] (delimiter is ',');


Final:

Load

RecNum,

    Contract_Num,

    Period,

    Amount,

    ForwardCumulAmt as data_test

Resident TestData

order by Contract_Num, Period desc;


tmp:

Load

    Contract_Num,

    Period,

    sum(Amount) as Amount_period

Resident TestData

Group by Contract_Num,

        Period

order by Contract_Num,

        Period desc;


drop table TestData;


Left join(Final)

Load Contract_Num,

    Period,

RangeSum(Amount_period, if(Peek('Contract_Num')=Contract_Num,Peek('ForwardCumulAmt'))) as ForwardCumulAmt

Resident tmp;


drop table tmp;

qcf is attached.

I didn't drop the original ForwardCumulAmt field (rename to data_test) for testing purpose.

G.

chuckklaniecki
Contributor II
Contributor II
Author

NICELY done, Gabor.

It took me two passes to discern your strategy.  But when I did, I saw the clean sense of it.  (I hope you make it to Qlik community level 8 very soon!)

MANY thanks!

undergrinder
Specialist II
Specialist II

Thank you Charles