Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mariesemikely
New Contributor

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

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,

5 Replies
MVP
MVP

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

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

mariesemikely
New Contributor

Re: Show the average time in the data loader

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.

MVP
MVP

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.

mariesemikely
New Contributor

Re: Show the average time in the data loader

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

MVP
MVP

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

Community Browser