Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Variables in data load editor

Hi,

I have created a variable in script. And I'm trying to use that variable in a where condition. I'm unable to get the result value from that variable.

 

let vd = 'max("Business Date")';
check:
load "Business Date",Sum(Usage)
Resident source
where "Business Date" = '$(vd)'
group by "Business Date";

I tried $(vd) and also '$(vd)'. Both are not working. I couldnt get the max date assigned in where condition.

Kindly provide me correct syntax.

 

Thanks !!

 

10 Replies
Highlighted
Creator II
Creator II

Re: Variables in data load editor

let vd = max("Business Date");
check:
load

"Business Date",

Sum(Usage)
Resident source
where "Business Date" = $(vd)
group by "Business Date";

Highlighted
Specialist
Specialist

Re: Variables in data load editor

You have to pass through an intermediate table:

 

TempTable:
Load
 Max(BusinessDate) as BusinessDate
Resident SourceTable:

Let vd=peek('BusinessDate',0,'TempTable');
Drop table TempTable

check:
Load 
    ....
    ....
    ....
Resident ....
  where BusinessDate='$(vd)'
   group by ...;
Highlighted
Creator
Creator

Re: Variables in data load editor

You need the equal sign in the defining variable part.

let vd = '=max("Business Date")';
check:
load

"Business Date",

Sum(Usage)
Resident source
where "Business Date" = $(vd)
group by "Business Date";
Highlighted
Partner
Partner

Re: Variables in data load editor

Qlik doew not know the max([Business Date]) in when used in the where. You will need another approach. You could try to figure out the maxvalue in advance so that vd contains the value and not the formula (Edit: I notice that @micheledenardi posted such a solution before I posted this answer). Or you could do an right join on it self using max(Business date) like I've done below.

 

SET vd = max("Business Date");
check:
load 
	"Business Date", 
	Sum(Usage) as Usage
inline [
	Business Date, Usage
	2019-01-01, 100
	2019-01-01, 100
	2019-02-01, 200
	2019-02-01, 200]
group by 
	"Business Date";
Right JOIN LOAD 
 $(vd) as [Business Date]
RESIDENT check;

 

 (Replace my inline with your own source data) 

 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor III
Contributor III

Re: Variables in data load editor

Hi Sameer,

I'm getting below error when changed the script as you mentioned:

The following error occurred:
Unexpected token: 'by', expected one of: 'bitand', 'bitor', 'bitxor', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', ...
 
The error occurred here:
check: load "Business Date", Sum("Usage - Free") Resident source where "Business Date" = group >>>>>>by<<<<<< "Business Date"
Highlighted
Contributor III
Contributor III

Re: Variables in data load editor

Hi Vegar,

Thanks for your script . It is working fine.

But, I have one more requirement:

max("Business date") is taking  the max date. But, I want it to change as per user selection.

Like, If I have dates from 1/1/2019 to 1/30/2019. 

By default, max("Business date") will take 1/30/2019.

If I select 1/10/2019 in filter, then I want max(Business date) to be changed to 1/10/2019.

Can you help me in getting this?

Highlighted
Contributor III
Contributor III

Re: Variables in data load editor

Hi michele,

I'm not getting max date after running the script

 

Highlighted
Specialist
Specialist

Re: Variables in data load editor

Try this, i've added a date() formatting during the valorization of the variable:

 

TempTable:
Load
 Max(BusinessDate) as BusinessDate
Resident Posting;

Let vd=date(peek('BusinessDate',0,'TempTable'),'DD/MM/YYYY');
Drop table TempTable;

check:
Load 
    ....
    ....
    ....
Resident ....
  where BusinessDate='$(vd)'
   group by ...;

 

 

Highlighted
Creator II
Creator II

Re: Variables in data load editor

Try this?

let vd = 'max([Business Date])';
check:
load
"Business Date",
Sum(Usage)
Resident source
where "Business Date" = $(vd)
Group By "Business Date";