Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Nested query

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jldengra
Creator
Creator

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.