3 Replies Latest reply: Aug 1, 2016 3:12 PM by Massimo Grossi RSS

    How to get minimum date in the granular level

    Sk Awal

      Hi All,

       

      I have a problem Like this:

      I have a date field which has all the update date and I have to take the Minimum Date from this field in the granular level.

      And i have a status field that has all the Status Code.

      Also another field "Processed" that has Boolean value T and F

      Now i have to make a field "Active date" in the script based on these three fields.

       

       

      Logic i am trying use is,

       

       

      IF(Match([Status],'A','P') and [Processed]='F' and [Update Date]=Min([Update Date]), [Update Date], ' ') as [Active Date]

       

       

      Seems like my logic is set on the Minimum Date from the Update date not the minimum date from the item/granular level for every products.

       

       

      How do i write down the code in the script that can work in the Item level? Meaning, getting the minimum date for every product.

       

      Thanks,

      Sk

        • Re: How to get minimum date in the granular level
          Gysbert Wassenaar

          Perhaps like this:

           

          SummaryTable:

          LOAD

               Product,

               Min([Update Date]) as [Active Date]

          FROM

               ...source...

          WHERE

               Match(Status, 'A','P') and Processed = 'F'

          GROUP BY

               Product

               ;

            • Re: How to get minimum date in the granular level
              Sk Awal

              Hi Gysbert,

               

              With the Where clause, Am i not limiting my table with the Match(Status, 'A','P') and Processed = 'F' filter?

              But my goal is to keep all the data and make a new filed Active date.


              Something like that

              IF(Match([Status],'A','P') and [Processed]='F' and [Update Date]=Min([Update Date]), [Update Date], ' ') as [Active Date]

              I know this piece of code is wrong but the idea is like that.


              Thanks,

              Sk

                • Re: How to get minimum date in the granular level
                  Massimo Grossi

                  You can add to your table a min update date, using a group by and left join (bold)

                   

                  yourtable:

                  load

                       product,

                       status,

                       processed,

                       updatedate

                  .......

                   

                  left join (yourtable)

                  load

                       product,

                       min(updatedate) as minupdatedate

                  resident yourtable

                  group by product;          // I think you want the min date by product

                   

                  then you can do a resident table with an if condition on status, processed, updatedate, minupdatedate to calculate the activedate field