topic Re: Question about direct query in QlikView

Question about direct query

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to use direct select in Qlikview Personal Edition 12.1

I have this query in scripts editor and it works fine:

ODBC CONNECT32 TO [odbc_consumo;DBQ=C:\.........\ficheros\modelo_estrella.mdb];

SQL SELECT cod_cliente,cod_periodo,cod_segmento,cod_tarifa,cod_terminal,hc_consumo

FROM hc_consumo;

Now I am trying to use in direct mode:

ODBC CONNECT32 TO [odbc_consumo;DBQ=C:\.........\ficheros\modelo_estrella.mdb];

DIRECT SELECT cod_cliente,cod_periodo,cod_segmento,cod_tarifa,cod_terminal,hc_consumo

FROM hc_consumo;

But it doesn' work.....teh error is

"No Direct statement

DIRECT SELECT cod_cliente,cod_periodo,cod_segmento,cod_tarifa,cod_terminal,hc_consumo

FROM hc_consumo"

Can anybody help me?

Re: Question about direct query

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 09:58:11 GMT

The DIRECT statement is to be used with the QUERY syntax instead of SQL SELECT. See here for more information:

QlikView Direct Discovery FAQ 11.2 SR7.pdf

Direct Discovery Technical Addendum_11.2_SR11_and_Sense.pdf

Direct Discovery ‒ QlikView‌

Direct Query ‒ QlikView

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 10:47:29 GMT

Thanks Miguel Angel,

One question:

When I work in direct query mode, is there anyway to regresh a graph when database data has changed.

Something similar to 'refresh' to see the new data in the graph

Thanks!!

Re: Question about direct query

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 11:21:14 GMT

Short answer is you don't need to reload, in general.

Longer answer is that since the values of the dimensions in the query are stored into the in-memory data model, and every time you click on the chart the SQL query is executed, as long as the dimensional values and fields in the chart remain the same, then there is no need to reload. QlikView will not know whether the data in the underlying database has changed, it sends the SQL query, and take the resulting values.

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 11:32:52 GMT

Hi Miguel Angel,

In my project , I don't see any refreh. I change the data o in Oracle database (commit changes(

and make a selection y mu qlik doc, but it remains the old values.

This is mu code:

ODBC CONNECT TO [odbc_oracle;DBQ=XE] (XUserId is cbdRGCdKfKQKEJdJcH, XPassword is cKSNOCdKSBMCTbAO);

Direct Query

Dimension COD_CLIENTE,COD_PERIODO,COD_TARIFA

Measure HC_CONSUMO

from DW_AERO.HC_CONSUMO;

Re: Question about direct query

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 11:42:10 GMT

If there are new values in COD_CLIENTE, COD_PERIODO or COD_TARIFA then it will not refresh, because those values MUST be in-memory, and therefore you need to reload the whole application.

If there are new values only in HC_CONSUMO, it should work.

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 11:53:59 GMT

I am only changing hc_consumo, I am updating and existing row in database and I only change

hc_consumo.

If I close and open the doc the the value refresh, but when I make selection it dosen't...

Do I have to change something in the chart?, do it depend on the odbc conector?

Thanks

Re: Question about direct query

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 12:12:11 GMT

I'd have to see the application and the connection to the database. Did you read through the documentation to see if any variable could help you in refreshing this data?

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 12:15:31 GMT

I can't share the doc because I am working with Personal Edition

Re: Question about direct query

Miguel_Angel_Baeyens — Fri, 27 Oct 2017 12:21:39 GMT

It could be the time the query is cached, using the variable DirectCacheSeconds, by default set to one hour.

I can say it works because I have several applications reading from HANA that we only reload once every day to refresh the master data, but the fact data remains (no new columns). HANA is updated more often and the charts display the right values (i.e.: same as in HANA).

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 12:24:21 GMT

Hi Miguel Angel,

Now it's working better, but not fine...

For example:

If I update database filed hc_consumo=value1 in a row of cod_periodo = 201706 and I make a selection in Qlikview in the chart for cod_periodo = 201706 then I see the new value, but when I return to the whole chart (no selection)

I see the old value (value0)

I make other change in hc_consumo=value2 in the same cod_periodo = 201706

then it doesn't change even if i make a selection I see the value of the first change (value1), and in the whole chart the old value (value0)

Is this the normal way of working of direct query??

Thanks

Re: Question about direct query

Anonymous — Fri, 27 Oct 2017 12:32:51 GMT

Thanks for your help Miguel Angel.

This was the problem

Re: Question about direct query

hugmarcel — Wed, 28 Mar 2018 20:33:03 GMT

Good Question. Did you ever get an answer to this?

Re: Question about direct query

Anonymous — Thu, 29 Mar 2018 07:58:33 GMT

Yes, it was a question of cache as it was answered in previous posts