Show the average time in the data loader

[Anonymous]

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,

[petter]

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...

[Anonymous]

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.

[petter]

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.

[Anonymous]

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

[petter]

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