6 Replies Latest reply: Oct 23, 2018 12:21 PM by Nagesh Setty RSS

    Dates help in backend

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have the source like below

       

      Source:

      Load * Inline [

      App,ID,Startdate,Enddate

      Banking,4586A,05/10/2018 00:00:00,08/10/2018

       

      Crime Analytics,5793B,25/07/2018 00:00:00,

      Crime Analytics,8794B,13/07/2018 00:00:00,

       

      HR,48632E,12/09/2018 00:00:00,13/09/2018

      HR,58632E,05/08/2016 00:00:00,

      HR,68632E,09/06/2018 00:00:00,

       

      Service,8632O,03/09/2018 00:00:00, 06/09/2018

      Service,86321,01/10/2018 00:00:00, 04/10/2018

      ];

       

      In the below input we have 8 rows. Here I need to keep only the rows where both startdate and enddate is not blank  by using backend script.

      Please help me on this.

      Please find the below attached app.



      Thanks in advance


      Input

      input.png

      Expected output


      expected output.png

        • Re: Dates help in backend
          Sunny Talwar

          Try this

           

          Load *

          Where Len(Trim(Startdate)) > 0 and Len(Trim(Enddate)) > 0;

          Load * Inline [

          App,ID,Startdate,Enddate

          Banking,4586A,05/10/2018 00:00:00,08/10/2018

           

          Crime Analytics,5793B,25/07/2018 00:00:00,

          Crime Analytics,8794B,13/07/2018 00:00:00,

           

          HR,48632E,12/09/2018 00:00:00,13/09/2018

          HR,58632E,05/08/2016 00:00:00,

          HR,68632E,09/06/2018 00:00:00,

           

          Service,8632O,03/09/2018 00:00:00, 06/09/2018

          Service,86321,01/10/2018 00:00:00, 04/10/2018

          ];

            • Re: Dates help in backend
              Mahitha M

              Hi Sunny,

               

              Thanks for your help.

              I need one more small help here from start date and End date please help me to calculate the difference in days.

              Here start date is in time stamp format and enddate is in DD/MM/YYYY format

              Load * Inline [

              App,ID,Startdate,Enddate

              Banking,4586A,05/10/2018 00:24:00,08/10/2018

              ];

                • Re: Dates help in backend
                  Sunny Talwar

                  Just in days? You can do

                  Floor(Startdate - Enddate) as DiffDays

                    • Re: Dates help in backend
                      Kingsley Hoare

                      Hi Sunny.

                       

                      Unfortunately that won't work as those fields aren't recognized as dates yet.

                       

                      Please add this to your preceding load:

                      DATE#(Enddate, 'DD/MM/YYYY')-DATE#(Startdate,'DD/MM/YYYY hh:mm:ss') as NumOfDays

                       

                      Also it will be End - Start instead of Start-End

                       

                      Many thanks

                      Kingsley

                    • Re: Dates help in backend
                      surendra j

                      Try like...

                      Load * ,interval(date(date#(Startdate,'DD/MM/YYYY HH:MM:SS'),'DD/MM/YYYY'),Enddate,'dd') as difference;

                      Load * Inline [

                      App,ID,Startdate,Enddate

                      Banking,4586A,05/10/2018 00:24:00,08/10/2018

                      ];

                       

                      --Surendra

                      • Re: Dates help in backend
                        Nagesh Setty

                        Can you try this script

                         

                         

                        
                        
                        DTTable:
                        Load *
                        , Enddate-Startdate AS Diff;
                        Load App,ID, DATE(Timestamp#(Startdate, 'DD/MM/YYYY hh:mm:ss'), 'MM/DD/YYYY') AS Startdate, DATE(DATE#(Enddate, 'DD/MM/YYYY'), 'MM/DD/YYYY') AS Enddate
                        Where Len(Trim(Startdate)) > 0 and Len(Trim(Enddate)) > 0;
                        Load * Inline [
                        App,ID,Startdate,Enddate
                        Banking,4586A,05/10/2018 00:00:00,08/10/2018
                        Crime Analytics,5793B,25/07/2018 00:00:00,
                        Crime Analytics,8794B,13/07/2018 00:00:00,
                        HR,48632E,12/09/2018 00:00:00,13/09/2018
                        HR,58632E,05/08/2016 00:00:00,
                        HR,68632E,09/06/2018 00:00:00,
                        Service,8632O,03/09/2018 00:00:00, 06/09/2018
                        Service,86321,01/10/2018 00:00:00, 04/10/2018
                        ];