Differenc between dates

[#1]

Hi community,

i want the difference of to dates with different values in a other coloum.

so for excample i have

article     info     date

apple     start     1/2/2012

cheese   start     3/5/2013    

cheese     end     6/7/2013    

apple     end     12/2/2016

and i want the difference between apple start and end.

So i have the following command for the date differenz:

date((date(Datum_VD,'YYYY/MM/DD')-date(Datum_VD,'YYYY/MM/DD')),'DD')

but i dont know how to say i want it for apple or cheese.

Thx for help

[marcus_sommer]

You could do it within a sorted resident load per Peek() or Previous() ? or you joined both dates together like:

temp:

Load article, date as startdate From xyz where info = 'start';

     left join

Load article, date as enddate From xyz where info = 'end'

final:

Load article, startdate, enddate, enddate - startdate as diffdate resident temp;

drop table temp;

- Marcus

[sunny_talwar]

May be this:

Table:

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

];

NewTable:

LOAD article,

  info,

  date,

  If(article = Peek('article'), Interval(date - Peek('date'), 'D')) as Difference

Resident Table

Order By article, date;

DROP Table Table;

[#4]

sorry i delete all answers my fault.

but now i have this error:

Der folgende Fehler ist aufgetreten:

Garbage after statement

Der Fehler ist hier aufgetreten:

temp: NOCONCATENATE load Artikelnummer,Datum as Wareneingang_datum where "Bezeichnung Buchungsart" ='Wareneingang' Resident InitialLoad

[marcus_sommer · Accepted solution]

Sorry, the source (resident) must be of course before the condition (where):

temp:

NOCONCATENATE load

     Artikelnummer,Datum as Wareneingang_datum

Resident InitialLoad

where "Bezeichnung Buchungsart" ='Wareneingang';

- Marcus

[#6]

Hi Marcus,

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

sold.

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

-Eric

[#7]

Hi Sunny ,

u helped me also a lot maybe u have an idea on how to solve my new problem under Marcus comment.

Thx in advance

-Eric