6 Replies Latest reply: Mar 18, 2016 10:24 AM by Eric Hannert RSS

    Differenc between dates

    Eric Hannert

      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

        • Re: Differenc between dates
          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

            • Re: Differenc between dates
              Eric Hannert

              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

                • Re: Differenc between dates
                  Marcus Sommer

                  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

                    • Re: Differenc between dates
                      Eric Hannert

                      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

                • Re: Differenc between dates
                  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;