Lookup returning null only in some cases

Anonymous — Mon, 01 Oct 2018 12:10:45 GMT

Hi everyone,

I'm trying to do some calculated fields with an expression based on the above row in a table. The script is a bit involved, so I'll try to only copy the relevant sections.

For anyone interested in what this formula is/does, this thread is the origin. The aim is to distribute a fee over some months where the fraction of fee in a monthh grows until it reaches a peak towards the end of the period. I have tried to include comments wherever needed.

My problem is that the fee for a month (after the first month of course) is based on the difference between cumulative value of current and previous month. I tried using peek but that returns odd results which lead to negative monthly fee(highlighted below, rightmost column). Lookup can't seem to find a match so returns null. Tried previous, above, before, all in the hopes of something working. Alas, no luck. The bolded bits are where I'm trying to show and solve the problem.

I have attached some sample data and a larger version of the screencap. I really hope the issue is something simple.

// Loading temp table

T1:

Load ProjectUID,

Date(FStartDate, 'DD/MM/YYYY') as MinDate, // start of the overall period

Date(FEndDate, 'DD/MM/YYYY') as MaxDate, // end of the overall period

if(IsNull(FProjTotalFee), 0, FProjTotalFee) as FeeValue // total value that needs to be distributed

resident ProjectForecastTbl; // table created previously in code

// Creates rows for each month from start to end date

MonthlyFees:

load *,

date(MonthEnd(AddMonths(MinDate, IterNo()-1, 1)), 'MM/YYYY') as MonthName,

RowNo() as RowNumber

Resident T1 while (month(AddMonths(MinDate, IterNo()-1, 1)) + 12*year(AddMonths(MinDate, IterNo()-1, 1))) <= (month(MaxDate)+12*year(MaxDate));

// add ID to table

Left Join(MonthlyFees)

load *,

Hash128(ProjectUID, MonthName) as FeeID

Resident MonthlyFees;

drop table T1;

// add current month and duration in months

left join(MonthlyFees)

Load FeeID,

ProjectUID,

MonthName,

MinDate,

12*(year(MonthName)-year(MinDate))+(month(MonthName)-month(MinDate)+1)as CurrPeriod, // calculates that current month is nth from period

12*(year(MaxDate)-year(MinDate))+(Month(MaxDate)-Month(MinDate)+1) as MonthDuration

resident MonthlyFees;

// calculation for T

left join(MonthlyFees)

Load *,

(CurrPeriod-2) / (MonthDuration-2) as T // refer to external link for formula if interested

Resident MonthlyFees;

// column for T

left join(MonthlyFees)

Load *,

num(if(T<0, 0, if(T>1, 1, 10*pow(T,2)*pow((1-T),2)*($(vA)+$(vB)*T)+pow(T,4)*(5-4*T)))) as CumulativeValue

// vA and vB set previously, refer to external link for formula if interested

resident MonthlyFees

order by RowNumber;

// add fee value

left join(MonthlyFees)

load *,

if(IsNull(LookUp(CumulativeValue, RowNumber, RowNumber-1)), 'No match', LookUp(CumulativeValue, RowNumber, RowNumber-1)) as deltaCumulativeValue, // this is to illustrate where the Lookup isn't working for some reason

money( if ((MonthDuration=1 or MonthDuration=2), if(CurrPeriod=MonthDuration, FeeValue, 0),

if(CurrPeriod=1, CumulativeValue*FeeValue, (CumulativeValue-peek('CumulativeValue'))*FeeValue)), '£##,###.00', '.', ',') as ForecastMonthlyFee

resident MonthlyFees;

Re: Lookup returning null only in some cases

Anonymous — Tue, 02 Oct 2018 08:52:14 GMT

For anyone interested, the problem originated from not specifying the fourth optional parameter in lookup().

Changing it to LookUp('CumulativeValue', RowNumber, PrevRow, 'MonthlyFees') fixed it. Even though it's the same table name that the function is in, apparently I had to specify it, probably because I'm working with joining fields on the same table over and over again.

I'd be curious to know if that is the case.

topic Re: Lookup returning null only in some cases in App Development

Lookup returning null only in some cases

Re: Lookup returning null only in some cases