Help with looking up values in the same table

[#1]

Hi All!

I have a question concerning the lookup() function or any other function that can assist me in attaining the following.

In one concatenated table is a column with cummulative values. I want to add a column to my table stating the non-cummulative values.

To establish this the value in month X from the origninal column should be deducted by the value of month X-1

Therefore, first I already established an extra column calculating month -1.  e.g. This has been performed. See example below

Month   |   Value   |    Month-1  

02               100          01                        

03               200          02

04               300          03                             

Now I want to add a column looking up the value for month-1 in the same table, but how can I establish that? Lookup(), ApplyMap()? See example below

Month   |   Value   |    Month-1    |   ValueMonth-1

02               100          01                         50

03               200          02                        100

04               300          03                         200

Thanks in advance!

[#2 · Accepted solution]

Here it goes

TmpFact:

LOAD Month,

     Value,

     (Month-1) as PreMonth

From (Excel file / QVD);

MapFact:

Mapping LOAD

     Month as ID,

     Value

Resident TmpFact;

Fact:

LOAD Month,

     Value,

     PreMonth,

     ApplyMap('MapFact',PreMonth,0) as PreValue

Resident TmpFact;

Drop Table TmpFact;

[Peter_Cammaert]

Actually, if you have Cumulative values, you should have actual values somewhere too?

In your case, forget about Month-1 and use Previous() function.

For example:

Facts:  // Only cumulative value
LOAD * INLINE [
Month, CumulValue, PrevMonth
02, 100, 01
03, 200, 02
04, 300, 03
05, 350, 04
06, 425, 05
07, 525, 06
08, 600, 07
09, 625, 08
10, 725, 09
11, 800, 10
12, 900, 11
];

QUALIFY *;

NewFacts:  // With absolute value
NOCONCATENATE
LOAD *,
    CumulValue - if(IsNull(Previous(CumulValue)),0, Previous(CumulValue)) as Value  // Account for Missing preceding record
RESIDENT Facts;

UNQUALIFY *;

Best,

Peter