Missing Data on dimenssions

[stabben23]

Hi, I have a problem to get missing data into my table, here is what i try to do:

MainTable:

What i have create:

Code:

MinMaxDate:

Load
min(Date) as MinDate,
Item,
Rate,
RowNo() as Rowno
resident TempTable_Rates
group by Item, Rate
order by Date desc;

Join Load
MinDate,
if(isnull(Previous(MinDate)),today()+1, Previous(MinDate)-1) as MaxDate
resident MinMaxDate

Problem is that i want to aggregate maxdate on Item, the highlighted MaxDate should be 2013-08-16 because no new "Date" is in the list for Item 10.

//Stabben

[marcus_malinow · Accepted solution]

Hi,

the issue here is that you are picking up Previous(Mindate) from the next row (i.e. the first with Item = 20.

I think what you should do is change your maxdate code to something like this:

if(isnull(Previous(MinDate)) or not(Item=Previous(Item),today()+1, Previous(MinDate)-1) as MaxDate

[stabben23]

That works perfect in my testcase, Thanks Marcus