You could do it within a sorted resident load per Peek() or Previous() ? or you joined both dates together like:
Load article, date as startdate From xyz where info = 'start';
Load article, date as enddate From xyz where info = 'end'
Load article, startdate, enddate, enddate - startdate as diffdate resident temp;
drop table temp;
i run around in my data and had another idea for something i can display.
What about a KPI showing the timerange in days an article was in store from delivery to the store till it was
So we have articlenumber, amount,sold/delivery, date.
The thing is that there can be 2 deliveries of a product before even one sales.
But with diffdate i only get the datedifferenc to the last deliverie of the same product.
So maybe create a var with the amount coming in from delivery and use this as the delivery date for the diffdate expression as long its not 0.And when its 0 switch to the next delivery date, so we allways have the right date.
And an if there is no delivery date before the sold-date i want to take the start date of the timerange.
The problem here, again i have a great idea but no clue about how i can achiev this in the right way.
So maybe u can give me some tipps.
Thanks for help in advance
May be this:
LOAD * Inline [
article, info, date
apple, start, 1/2/2012
cheese, start, 3/5/2013
cheese, end, 6/7/2013
apple, end, 12/2/2016
If(article = Peek('article'), Interval(date - Peek('date'), 'D')) as Difference
Order By article, date;
DROP Table Table;