Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP
MVP

Re: Nested query

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
3 Replies
Not applicable

Re: Nested query

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

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

Some tips ?

MVP
MVP

Re: Nested query

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
Contributor

Re: Nested query

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.