Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
chuckklaniecki
New 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
Valued Contributor II

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

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
Valued Contributor II

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

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

chuckklaniecki
New Contributor II

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

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
Valued Contributor II

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

Thank you Charles