topic Re: Get fields for a max calculated value in QlikView

Get fields for a max calculated value

Wed, 05 Dec 2012 16:12:56 GMT

Hi,

I have a table with theses field and an example:

SNumber Date Val Step 1 Date Val Step 2 Service Code

11111 10/05/12 10/15/12 1

11111 10/05/12 10/15/12 2

11111 10/05/12 10/12/12 4

11111 10/05/12 10/18/12 6

I would like to make the Duration of the difference between theses dates and take the Max of this Duration

Like:

SNumber Date Val Step 1 Date Val Step 2 Service Code Duration

11111 10/05/12 10/15/12 1 10

11111 10/05/12 10/15/12 2 10

11111 10/05/12 10/12/12 4 7

11111 10/05/12 10/18/12 6 13

So take the max of this value and and at the final just:

11111 10/05/12 10/18/12 6 13

For that in my table, I put the expression:

avg(aggr(max(Duration),SNumber))

But my code doesn't work, I have the good duration and the good Max, but the Service Code for the Max Duration is not good ....

I have just:

11111 10/05/12 10/18/12 1 13

This is my code:


D_WORKFLOW_TMP: LOAD SNumber, [Date Val Step 1] FROM $(vFile) (qvd) WHERE [Code Etape] = 4; JOIN LOAD SNumber, [Service Code], [Date Val Step 2] FROM $(vFile) (qvd) WHERE [Code Etape] = 6; D_WORKFLOW: LOAD SNumber, [Service Code], if(isnull([Date Val Step 2]),[Date Val Step 1] - [Date Val Step 1], [Date Val Step 2] - [Date Val Step 1] ) AS Duration, [Date Val Step 2], [Date Val Step 1] RESIDENT D_WORKFLOW_TMP;

D_WORKFLOW_TMP:
LOAD
SNumber,
[Date Val Step 1]
FROM $(vFile) (qvd)
WHERE [Code Etape] = 4;

JOIN LOAD
SNumber,
[Service Code],
[Date Val Step 2]
FROM $(vFile) (qvd)
WHERE [Code Etape] = 6;

D_WORKFLOW:
LOAD
SNumber,
[Service Code],
if(isnull([Date Val Step 2]),[Date Val Step 1] - [Date Val Step 1],
[Date Val Step 2] - [Date Val Step 1]
) AS Duration,
[Date Val Step 2],
[Date Val Step 1]
RESIDENT D_WORKFLOW_TMP;

Can you help me with my problem please?

Re: Get fields for a max calculated value

Gysbert_Wassenaar — Wed, 05 Dec 2012 17:56:59 GMT

try aggr(firstsortedvalue([Service Code],-Duration),SNumber)

Re: Get fields for a max calculated value

Mon, 10 Dec 2012 08:53:14 GMT

Thanks for the answer.

It's not working, I tried aggr(firstsortedvalue([Service Code],-Duration),SNumber) and aggr(max(firstsortedvalue([Service Code],-Duration)),SNumber) but no one works in the expressions.

Do you have an another idea please?

Re: Get fields for a max calculated value

Mon, 10 Dec 2012 09:53:14 GMT

Oh okey; now with all field with this expression, it's better, itùs working on some of it. But some other are still empty ..... I don' know why.

For exemple if I have:

11111 02/27/2009 02/20/2009 2 7

11111 02/27/2009 02/20/2009 3 7

11111 03/10/2009 02/20/2009 8 18

I Should have:

11111 03/10/2009 02/20/2009 8 18

But I have an empty values on all the fields in expression. Except the SNumber in dimention ....

But for exemple:

11112 03/11/2009 03/10/2009 2 1

11112 03/11/2009 03/10/2009 8 1

11112 03/12/2009 03/10/2009 3 2

This one is working great:

11112 03/12/2009 03/10/2009 3 2

So .... I don't understand why one works and not an another one ... And itùs like half working and half not ...

Re: Get fields for a max calculated value

Gysbert_Wassenaar — Mon, 10 Dec 2012 10:48:05 GMT

Maybe the max duration occurs in several records. In that case the firstsortedvalue cannot determine which one should be used so it returns null.

Re: Get fields for a max calculated value

Mon, 10 Dec 2012 10:52:59 GMT

Yeah, I put a DISTINCT aggr(firstsortedvalue(DISTINCT [Service Code],-Duration),SNumber), and it seems to work.

Thank you very much