condition with Variable / Dynamic Where Condition

[Samanehsorournejad]

Hi every one,

I have problem with my qlik sense Script, I want to filter the date before that I send it to my dashboard. I had write this

query but it does not Answer. Is there any one who could tell me why?

I had define two Variable :

LET vTo= date(Max(zeitpunktverarbeitungdate),'DD.MM.YYYY')-1;
LET vFrom=date(Max(zeitpunktverarbeitungdate),'DD.MM.YYYY')-31;

and then I had read my table:

load * Resident milchkontrolle
where zeitpunktverarbeitungdate >= $(#vFrom) and zeitpunktverarbeitungdate <= $(#vTo);

it does not give me any error but my table after load script is empty.

Can Someone help me 😞

Thanks in advanced

[RafaelBarrios · Accepted solution]

hi @Samanehsorournejad 

That not the right way to read from a table

for that you need to use peek() funtion

LET vTo= date(peek('date_field',position,'table')-1,'DD.MM.YYYY');

Best,

[RafaelBarrios]

Something like this

tmp_milchkontrolle:
Load 
     max(zeitpunktverarbeitungdate) as maxdate
resident <where you are trying to read date from>;

let vTo = date(peek('maxdate',0,'tmp_milchkontrolle')-1,'DD.MM.YYYY');
let vFrom = date(peek('maxdate',0,'tmp_milchkontrolle')-31,'DD.MM.YYYY');

DROP TABLE tmp_milchkontrolle;   //no needed anymore

considere the following:
when you do a max to a date field it will convert it to number. so you did right by using the date(), but when you do -1 or -31 it convert it again to number.

and then try not use $(#vFrom) and $(#vTo)

load * Resident milchkontrolle
where zeitpunktverarbeitungdate >= $(vFrom) and zeitpunktverarbeitungdate <= $(vTo);

hope this helps.

Best, 

[Samanehsorournejad]

Hi and thanks for your answer, I had done your idea but again error 😞

[RafaelBarrios]

Hi @Samanehsorournejad 

two question

whats the date format of zeitpunktverarbeitungdate ?
Whats your app date format ?

while you aswer that, you can try

load * Resident milchkontrolle
where zeitpunktverarbeitungdate >= date#($(vFrom),'DD.MM.YYYY') and 
zeitpunktverarbeitungdate <= date#($(vTo),'DD.MM.YYYY');

or, i thinking that is not necesary to do date#($())

load * Resident milchkontrolle
where zeitpunktverarbeitungdate >= vFrom and 
zeitpunktverarbeitungdate <= vTo;

Best