Variables in data load editor

[Prink]

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

[Sameer9585]

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

"Business Date",

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

[micheledenardi]

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 ...;

[Edvin]

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";

[Vegar]

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) 

[Prink]

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"

[Prink]

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?

[Prink]

Hi michele,

I'm not getting max date after running the script

[micheledenardi]

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 ...;

[Sameer9585]

Try this?

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