Referring other columns each other

[M_Jay]

Hi,

In a table the "End" value is the "Beginning" value for the following month.

       Beginning  =  Previous(End)

The "End" value is based on the "Beginning" of the current month as shown below.

    End = Beginning - Sold + Income

The Sold and Income data are given for all months

I am trying to do it in Load Script which is challenging for me. If any help for the chart is also welcome.

Thanks

MJ

[marcus_sommer · Accepted solution]

You may try something like this:

load Year, Month, Sold, Income,
          rangesum(peek('End')) as Beginning,
          rangesum(peek('End'), -Sold, Income) as End
resident Source order by Year, Month; // Month should be numeric

[brunobertels]

Hi Marcus 

just GENIOUS how you did it with 2 lines of script !!!! 

if you can , would you explain what is the logic with rangesum and peek please 

i reached the desired output writing this "LONG" script : 

[Table]:
load *,
rowno() as ID,
peek('END') as BEGINNING;
load *,

sold + income as END;
LOAD * INLINE
[
mois,sold,income
janvier,0,20
février,18,20
mars,18,18
avril,15,47
mai,20,40
juin,18,50
juillet,30,40
août,50,30
septembre,60,20
octobre,30,15
novembre,20,12
décembre,35,10
janvier,50,50
](delimiter is ',');

temp:

load
ID,
mois,
sold,
income,
// previous(BEGINNING) as BEGINNING,

if(isnull(BEGINNING) , END, peek('END') - sold + income) as END

resident Table order by ID asc ;
drop table Table;

final :
load * ,peek('END') as BEGINNING
resident temp ; drop table temp;

it give me this table  but with a large effort !! 

[marcus_sommer]

At first, by applying interrecord-functions the wanted load-order must be ensured which leads in the most scenarios to the necessity to use a resident load with an order by clause. After that peek() and previous() could access each already loaded record. But the first record has no previous record and an attempted access will lead to NULL - which may also occur if there are multiple level and/or further conditions which may not always TRUE.

Such "exceptions" might be fetched in beforehand or afterwards by checking the rowno() or the result but much easier is most to apply functions which provide a default-value parameter like: alt() or coalesce() or are treating all none-numeric parameter as ZERO like the range-functions. In general are the range-functions very powerful to avoid a lot exception fetching if-loops - not only in regard to NULL else also to set upper/lower thresholds with nested rangemin() and rangemax() and many more usecases.

[brunobertels]

Thanks Marcus it is clear and I learned somethink very usefull 

[M_Jay]

Thanks Marcus. It is very helpful