Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
let vd = max("Business Date");
check:
load
"Business Date",
Sum(Usage)
Resident source
where "Business Date" = $(vd)
group by "Business Date";
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 ...;
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)
Hi Sameer,
I'm getting below error when changed the script as you mentioned:
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?
Hi michele,
I'm not getting max date after running the script
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 ...;
Try this?
let vd = 'max([Business Date])';
check:
load
"Business Date",
Sum(Usage)
Resident source
where "Business Date" = $(vd)
Group By "Business Date";