App Development

[chuckklaniecki]

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

[undergrinder · Accepted solution]

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]

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]

Thank you Charles