progressive sum in ETL process

[Anonymous]

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

IB

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...

[MK_QSL]

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;

[hic]

It the records are ordered, you can use

RangeSum(total_month,Peek(accumulated)) as accumulated

HIC

[MK_QSL · Accepted solution]

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

[Anonymous]

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

IB