topic Re: progressive sum in ETL process in QlikView

progressive sum in ETL process

Anonymous — Tue, 18 Mar 2014 14:37:18 GMT

Hi Community,

I'm wondering about creating a field which contains a progressive sum of values in another field, during the ETL process. I explain what I mean with the following example:

table:

LOAD yearmonth,

right(yearmonth, 2) as month,

total_month

FROM fonte.qvd (qvd);

this table looks like:

Well, now I'd like to add to this table a new field 'progressive_total_month', that has in the first row the value 165, in the second row the value 165+156, in third row the value 165+156+172, and so on...

Me, I was thinking about a FOR cycle, but I understand it's not applicable "within" a LOAD...

Is there any instrument or function in QV that can help me resolve this problem?

Thanks to everyone's going to answer.

Greetings

ps: Till now, the only solution I've found is the following:

if(yearmonth='201001', total_month,

if(yearmonth='201002', total_month+ previous(total_month),

if(yearmonth='201003', total_month+ previous(total_month)+ previous(previous(total_month)), ...) ) )

as progressive_total_month

but it's quite impracticable...

Re: progressive sum in ETL process

MK_QSL — Tue, 18 Mar 2014 14:42:08 GMT

table:

LOAD yearmonth,

right(yearmonth, 2) as month,

total_month

FROM fonte.qvd (qvd);

Final:

Load

yearmonth

month,

IF(Month+1 = Previous(Month), total_month + Previous(progressive_total_month), total_month) as progressive_total_month

Resident table

Order By yearmonth, month;

Drop Table table;

Re: progressive sum in ETL process

hic — Tue, 18 Mar 2014 14:42:56 GMT

It the records are ordered, you can use

RangeSum(total_month,Peek(accumulated)) as accumulated

HIC

Re: progressive sum in ETL process

MK_QSL — Tue, 18 Mar 2014 14:46:34 GMT

Sorry little change in my script.

Temp:

Load * Inline

[

yearmonth, month, total_month

201001, 1, 165

201002, 2, 156

201003, 3, 172

201004, 4, 166

201005, 5, 167

201006, 6, 154

201007, 7, 162

201008, 8, 87

201009, 9, 146

201010, 10, 170

201011, 11, 183

201012, 12, 173

];

Final:

Load

yearmonth,

month,

if(RowNo()=1, total_month, Peek('progressive_total_month') + total_month) as progressive_total_month

Resident Temp

Order By yearmonth;

Drop Table Temp;

=============================

UPDATE : Please check enclosed file which will give you idea how to get your requirements using Script and UI end both

Re: progressive sum in ETL process

Anonymous — Tue, 18 Mar 2014 15:17:14 GMT

Hello Manish,

I've just checked out your solution and it works perfectly...

So thank you so much, I'm going to explore the function peek(), which is new for me...

Greetings