topic Straight Table Question in QlikView

Straight Table Question

Tue, 11 Mar 2014 18:15:39 GMT

Hello,

I have a table with the following information:

JOB_NAME	Max Elapsed	DateTime
A	00:12:51
D	00:06:30
F	00:03:01
G	00:02:59
H	00:02:46
X	00:01:50	11/03/2014 02:00:00

I need to print in DateTime the value relative to when the Max Elapsed moment happens, Function: (=max(DURATION))

Only the JOB_NAME=X works, i think because is only a dot and no aggregate fuction is used.

Can you help me?

Thanks!!

Re: Straight Table Question

Tue, 11 Mar 2014 18:47:20 GMT

Hey Nico,

Can you attach a table of how the result should look like.Can you also include Duration field in the table ? It will be helpful in analyzing further.

Re: Straight Table Question

Tue, 11 Mar 2014 18:56:34 GMT

Thanks

JOB_NAME	Max Elapsed	DateTime	Duration
A	00:12:51
D	00:06:30
F	00:03:01
G	00:02:59
H	00:02:46
X	00:01:50	11/03/2014 02:00:00	00:01:50

My dimension is JOB_NAME, I expected similar results like row X.

The chart is a TOP 10 of Max Elapsed times for data base batch executions. I need to show the batch name, the elapsed time and when the situation was.

Thanks!

Re: Straight Table Question

Tue, 11 Mar 2014 19:08:51 GMT

Can you attach your sample qvw file if possible. I think I am missing something but I cant quite understand how Date Time is calculated.

Re: Straight Table Question

Tue, 11 Mar 2014 19:19:53 GMT

DateTime is not calculated, is the data loaded directly from the data base.

"Max Elapsed" is the label for max(DURATION)

Thanks!

Re: Straight Table Question

its_anandrjs — Tue, 11 Mar 2014 19:31:58 GMT

Hi,

I want to know the fields JOB_NAME, Max Elapsed, Date time and Duration all is from data base or in other way which is calculated field among this.

Thanks & Regards

Re: Straight Table Question

Tue, 11 Mar 2014 20:44:01 GMT

Hi Anand,

JOB_NAME= From Data Base (Dimension of the chart)

Max Elapsed= max(DURATION) *

Date time= From Data Base

*DURATION= From Data Base

Thanks!

Re: Straight Table Question

swuehl — Tue, 11 Mar 2014 20:47:09 GMT

Try

=FirstSortedValue( DateTime, -DURATION)

as expression to retrieve the DateTime value for the max DURATION.

Re: Straight Table Question

Wed, 12 Mar 2014 12:37:21 GMT

Hi swuehl,

That solution was pretty good, thanks!

I dont know why, but for same cases I don't get DateTime value.

JOB_NAME	Max Elapsed	DateTime
A	00:12:51	11/03/2014 02:00:00
D	00:06:30	11/03/2014 02:02:00
F	00:03:01	11/03/2014 02:03:00
G	00:02:59	11/03/2014 02:04:00
H	00:02:46	-
X	00:01:50	11/03/2014 02:00:00

For example for row H I have 4 DateTime for it, may be the last should be selected?:

10/03/2014 19:41:30

10/03/2014 19:45:30

11/03/2014 01:32:00

11/03/2014 01:44:00

Cheers

Re: Straight Table Question

swuehl — Wed, 12 Mar 2014 19:37:29 GMT

With same cases, you mean the records share the same DURATION?

That's an issue with FirstSortedValue(), it will return NULL when there are more than 1 record with the same sort weight.

You can use

=FirstSortedValue(DISTINCT DateTime, -DURATION)

to get a value back, but the will not necessarily return the latest DateTime.

You can consider adding a small number to your sort weight based on DateTime to distinguish between the DateTime values.