Nested query

[#1]

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.

ID	Value	Date
Record_1	10	31/05/2015
Record_1	15	30/06/2015
Record_1	20	31/07/2015
Record_2	7	30/06/2015
Record_3	5	31/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

[#2]

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

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

Some tips ?

[jonathandienst · Accepted solution]

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.

[jldengra]

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.