5 Replies Latest reply: Dec 10, 2012 5:52 AM by guillaumek

# Get fields for a max calculated value

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:
SNumber,
[Date Val Step 1]
FROM \$(vFile) (qvd)
WHERE [Code Etape] = 4;

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

D_WORKFLOW:
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

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

• ###### Re: Get fields for a max calculated value

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

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

:S

• ###### Re: Get fields for a max calculated value

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

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

Thank you very much