3 Replies Latest reply: Aug 28, 2015 3:23 AM by Jose Luis Dengra RSS

    Nested query

    Pierre Rigaud

      Hi everyone,

       

      I would like to do a nested query, but I really don't know how to do it in my LOAD.

      I have a table with a kind of historization of the evolution oh each ID status.

       

      IDValueDate
      Record_11031/05/2015
      Record_11530/06/2015
      Record_12031/07/2015
      Record_2730/06/2015
      Record_3531/05/2015

       

      The user picks up one date, and I want to have the latest status of the record relative to this date.

      In our example the user picks up the date : 30/06/2015.

       

      So I want to get :

      -     Record_1 ==> 15

      -     Record_2 ==> 7

      -     Record_3 ==> 5

       

      The sql request could be :

      SELECT ID as Record_id, Value

      FROM Table

      WHERE Date = max( SELECT Date FROM Table WHERE Date <= date_picked_By_user AND ID = Record_id)

       

      I don't know how to do the part in red in my LOAD

       

      Thanks you

        • Re: Nested query
          Pierre Rigaud

          I tried something like thatm but there are some script errors :

           

          where Date = FirstSortedValue( LOAD Date WHERE Date <= '$(v_Month_report)' , ID  ).

           

          Some tips ?

            • Re: Nested query
              Jonathan Dienst

              You can't use that in a SQL SELECT - everything after SQL to the end of the statement is passed to your DBMS to execute, and it cannot execute QV expressions.

               

              Perhaps something like this - the preceding load will get the most recent and the SQL where clause will limit the search to dates after the fence in vDate:

               

              Let vDate = '2015/07/01'; // this would be adjustable from a calendar object or input box

               

              LOAD Record_id,

                max(Date) As Date,

                FirstSortedValue(Value, -Date) As Value

              Group By Record_id;

              SQL SELECT ID as Record_id,

                Date,

                Value

              FROM Table

              Where Date >= '$(vDate)';

               

              Note:  format vDate in a way that is recognised by the DMBS, or use the DBMS CONVERT... or CAST... functions to convert to a suitable date value.

            • Re: Nested query
              Jose Luis Dengra

              If you need this values for some expression, you can get them by making use of the function FirstSortedValue.

              In order to apply descending sorting, your expression could be similar to the following:

               

              FirstSortedValue(Value, -Date)


              If you need these values to be loaded in the data model instead of calculated, my suggestion would be to transpose the data into a different table and make use of one of the transposed columns to determine the value for each record.