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

    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?

        • Re: Get fields for a max calculated value
          Gysbert Wassenaar

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

            • Re: Get fields for a max calculated value

              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

                  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