Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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?

Not applicable
Author

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Thank you very much