topic Re: Passing a variable from input box to SQL in QlikView

Passing a variable from input box to SQL

Wed, 10 Oct 2012 09:16:11 GMT

Hi everybody,

I am trying to assign a value to a variable by creating an input box on the sheet, and then using that variable in the SQL select statement as a constraint.

I believe I have the variable correctly. When I go to the variable tab at the bottom of the edit script I see the value of basTarih as 2012-10-01.

When I try to run the code this way I get an error saying that it cannot convert basTarih to timestamp. In the onput box properties number tab I've selected both date and timestamp formats for basTarih but none seem to work.

Do I need a SET statement somewhere?

siparis:

LOAD *;

SQL SELECT ol_or_no1, ol_or_no2, ol_sr, ol_ma_kod AS ma_kod, ol_qty, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, date(ol_wrk_basdt), date(ol_onaydt),date(ol_terdt),

ol_or_anhno, ol_onay2, date(ol_onay2dt),or_pe_no, or_anhno, or_rr_no

FROM DBA.ordln

JOIN DBA.ord

ON ordln.ol_or_anhno = ord.or_anhno

WHERE ol_kapa = 'A' AND (date(ol_onaydt) BETWEEN '&basTarih' AND '2012-10-08')

order by ol_or_no2, ma_kod, desen, varyant

;

Thanks for your help.

Re: Passing a variable from input box to SQL

Miguel_Angel_Baeyens — Wed, 10 Oct 2012 09:22:01 GMT

Hi,

If the variable already exists in your input box in the design, you can check its contents in the Settings menu, Variable Overview. If that's OK, then the SQL should look like the following:

WHERE ol_kapa = 'A' AND (date(ol_onaydt) BETWEEN '$(basTarih)' AND '2012-10-08')

So QlikView returns the value of the variable and so it's passed on to the ODBC driver and the database.

Hope that helps.

Miguel

Re: Passing a variable from input box to SQL

Wed, 10 Oct 2012 09:24:56 GMT

Thanks Miguel, I've initially tried the $(basTarih) but I forgot he single quotes, thanks again.