Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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...
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.
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.
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
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.
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