topic Re: Help with looking up values in the same table in QlikView

Help with looking up values in the same table

Thu, 17 Oct 2013 06:33:45 GMT

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!

Re: Help with looking up values in the same table

Thu, 17 Oct 2013 07:01:20 GMT

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;

Re: Help with looking up values in the same table

Peter_Cammaert — Thu, 17 Oct 2013 07:32:55 GMT

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