Create a conditional column based on max(value) of other column

[mikael-c]

I'm plotting a line graph which contains a lot of historical data. To make it a little easier to read, I wish to only show the latest 48 hours in this chart. New data is added every hour so I cannot make a static filter.

To do this, I thought of using the "Actions" in the sheet parameters and selecting by default the last data and then the user can change the selection to his/her liking. To make use of this Action, the way I understand it, I have to select some value in the table. So I thought of adding a column with the value 1 if the timestamp of the data is in the last 48 hours, if not a 0.

This is what my load script looks like:

[table_temp]:

LOAD

[date] as [date], //as YYYY-MM-DD

[hour] as [hour], //as hh

[id] as [id],

[value] as [value],

timestamp(date#([date], 'YYYY-MM-DD')+time#([hour] & ':00', 'hh:mm'), 'YYYY-MM-DD hh:mm') as [data_timestamp] //converting the 2 columns [date] and [hour] to timestamp

FROM ... 

;

Let vLastTimestamp = '=max([data_timestamp])';

//also tried "Set" and all the variants with and without the quotes and the equal sign

NoConcatenate

[table]:

load *,

if([data_timestamp] >= timestamp('$(vLastTimestamp)'-2), 1, 0) as [recent_data]

Resident [table_temp]

order by [recent_data] desc, [id] asc;

drop table [table_temp];

But I only get a 0, never a 1! It's like if the formula doesn't work.

However, if I create a table in my sheet and then add this column to test:

=if([data_timestamp] >= timestamp(vLastTimestamp-2), 1, 0)

it works!!!!!!!! (first column in the screenshot is created in the load script and last is just the formula added in the sheet, others are tests - i changed the column names in my post 🙂 )

What's the problem??!?!

[stevejoyce · Accepted solution]

Your variable is being stored as an expression, not a value.  And the load script is not evaluating the variable.  

Try this:

[table_temp]:

LOAD

[date] as [date], //as YYYY-MM-DD

[hour] as [hour], //as hh

[id] as [id],

[value] as [value],

timestamp(date#([date], 'YYYY-MM-DD')+time#([hour] & ':00', 'hh:mm'), 'YYYY-MM-DD hh:mm') as [data_timestamp] //converting the 2 columns [date] and [hour] to timestamp

FROM ... 

;

max_timestamp:

load

timestamp(max(timestamp)) as max_timestamp

resident [table_temp]

;

let vLastTimestamp = peek('table_temp', 0, 'table_temp');

//also tried "Set" and all the variants with and without the quotes and the equal sign

NoConcatenate

[table]:

load *,

if([data_timestamp] >= timestamp('$(vLastTimestamp)'-2), 1, 0) as [recent_data]

Resident [table_temp]

order by [recent_data] desc, [id] asc;

drop table [table_temp];

[mikael-c]

You're right, it does work.

Changed the answer to:

max_timestamp:

load

timestamp(max(data_timestamp)) as max_timestamp

resident [table_temp]

;

let vLastTimestamp = peek('max_timestamp', 0, 'max_timestamp');

However, I don't get why I need to create yet another table with the maximum timestamp value before getting the max value. Also why this temp table is a resident table? And why for other variables I can just use Let vFoo = '=max(value)' and here I cannot?

🤔 qlik sense 🙄

[stevejoyce]

You can drop the max_timestamp table after you set your variable.  That's how you do aggregations in the load script.  You can read up on variables Let vs Set.  Even though your using Let for vFoo, your setting the value to a string...look at vFoo in your variable editor view after script is done, it's not set to a value, it's set as an expression, so depending on your filter selections vFoo can update.  If you want to store the max value at a time in the load script, how i mentioned is how you can do that.

You're right on switching to having peek() look at the max_timestamp table, thanks for catching that and adjusting