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

# Show the average time in the data loader

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]';

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

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])';

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

Hi,

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

GROUP BY;

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

if([Field Name] = 'Value'

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

Thanks again.

• ###### Re: Show the average time in the data loader

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

[Priority],

[Submit 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])';

if([Field Name] = 'Value'

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

• ###### Re: Show the average time in the data loader

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