5 Replies Latest reply: Jan 5, 2018 7:30 AM by Petter Skjolden RSS

    Show the average time in the data loader

    Mariese Mikely

      Hi,

       

      I would like to add an additional column to my table, to show the average time between the 'Open' date and 'Last Modified' date per value. As shown below:

       

      DERIVE FIELDS FROM FIELDS %[Open Date and Time], %[Last Modified Date] using Table

      Let vOpenDate = 'Avg([Open Date and Time]';

      Let vModifiedDate = 'Avg([Last Modified Date]';

      if ([Field Name] = 'Value Name',

      vModifiedDate - vOpenDate as Avg_Time

       

      However it is not working, would you be able to advise where appropriate?

       

      Thanks,

        • Re: Show the average time in the data loader
          Petter Skjolden

          Here are some problems which I can spot:

           

          1) The DERIVE FIELDS statement has to be terminated with a semicolon.

           

          2) If you use Avg() aggregation function it has to be used with a load statement which has a GROUP BY clause.

           

          3) You are missing ending paranthesis in both of the LET statements:

           

                  Let vOpenDate = 'Avg([Open Date and Time])';      

                  Let vOpenDate = 'Avg([Last Modified Date])';

           

          4) If you refer to variables in a load statement you will have to use $-sign expansion to retrieve the values inside the variables:

           

          If( [Field Name] = 'Value Name' , $(vModifiedDate) - $(vOpenDate) ) AS AvgTime.

           

          Even here you are missing an ending paranthesis - which I have added in red...

            • Re: Show the average time in the data loader
              Mariese Mikely

              Hi,

               

              Thank you for your response, I've made the adjustments you've stated above, but I'm still recieving errors within the data loader. Please see below:

              DERIVE FIELDS FROM FIELDS %[Open Date and Time]; %[Last Modified Date] USING TABLE;

              GROUP BY;

              Let vOpenDate = 'Avg([Open Date and Time])';

              Let vModifiedDate = 'Avg([Last Modified Date])';

              if([Field Name] = 'Value'

              $(vModifiedDate) - $(vOpenDate)) AS Avg_Time

               

              Thanks again.

                • Re: Show the average time in the data loader
                  Petter Skjolden

                  The GROUP BY can stand on it's own like you have done. It has to be at the end of a LOAD statement. You don't have a load statement that you show in the snippet above. Could you share the load statement where you want to use the

                  $(vModifiedDate) - $(vOpenDate) AS Avg_Time where also the GROUP BY needs to be at the end.

                    • Re: Show the average time in the data loader
                      Mariese Mikely

                      Please see below:

                       

                      LOAD

                      [Priority],

                      [Submit Date],

                      [Last Modified Date],

                      [Customer group],

                      [Organization],

                      [Department],

                      DERIVE FIELDS FROM FIELDS %[Open Date and Time]; %[Last Modified Date] USING TABLE;

                      GROUP BY

                      Let vOpenDate = 'Avg([Open Date and Time])';

                      Let vModifiedDate = 'Avg([Last Modified Date])';

                      if([Field Name] = 'Value'

                      $(vModifiedDate) - $(vOpenDate)) AS Avg_Time

                        • Re: Show the average time in the data loader
                          Petter Skjolden

                          With all due respect.

                           

                          You are mixing various kind of statements in a rather free (random) way. To be able to write working scripts you will have to learn how to construct valid and syntactically correct statements.

                           

                          1. LOAD can't have a DERIVE FIELDS FROM FIELDS statement.
                          2. GROUP BY can't be written with a Let.
                          3. The GROUP BY needs a list of fields following it
                          4. The If statement should be part of a load statement
                          5. As a general rule most statements need to be terminated by a semicolon

                           

                          To me it seems like you need to learn the basics of scripting before you embark on more advanced scripting. You will never succeed by doing too much trial and error.

                           

                          regards

                          Petter