topic Re: condition with Variable / Dynamic Where Condition in App Development

condition with Variable / Dynamic Where Condition

Samanehsorournejad — Mon, 17 Oct 2022 12:38:28 GMT

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

Re: condition with Variable / Dynamic Where Condition

RafaelBarrios — Mon, 17 Oct 2022 17:49:55 GMT

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,

Re: condition with Variable / Dynamic Where Condition

RafaelBarrios — Mon, 17 Oct 2022 18:03:15 GMT

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,

Re: condition with Variable / Dynamic Where Condition

Samanehsorournejad — Tue, 18 Oct 2022 06:34:37 GMT

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

Re: condition with Variable / Dynamic Where Condition

RafaelBarrios — Tue, 18 Oct 2022 08:01:16 GMT

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