topic Re: qvd file without information in QlikView

qvd file without information

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

Hello everyone,

I have the following script, it works without errors, but it doesn't return any data.

Basically, I connect to an MS Access query and define a Start Date and an End Date to bring information.

the result of the log is:

Connecting to MS Access Database;DBQ=C:\Users\Alexis\Desktop\Qlik\base de datos\Trx.accdb

Connected

trx << 021_Trx_Totales 0 Lines fetched

--- Ejecución de Script finalizada ---

I hope someone can help me. Thank you...

LET vFechaInicioCarga = date(date#(01072018, 'DDMMYYYY'), 'DD-MM-YYYY');

LET vFechaFinCarga = date(MonthEnd(vFechaInicioCarga),'DD-MM-YYYY');

LET vPeriodoCarga = num#(date(vFechaInicioCarga, 'YYYYMM'));

ODBC CONNECT32 TO [MS Access Database;DBQ=C:\Users\Alexis\Desktop\Qlik\base de datos\Trx.accdb];

trx:

SQL SELECT

fecha,

generico,

Id,

sentido,

servicio,

"tipo_dia",

"Trx_Totales",

unidad,

variante

FROM "021_Trx_Totales"

where fecha >=$(vFechaInicioCarga) and fecha <=$(vFechaFinCarga);

STORE trx into [..\Qlik\transacciones_$(vPeriodoCarga).qvd];

drop table trx;

Re: qvd file without information

Lisa_P — Tue, 18 Sep 2018 03:42:44 GMT

It must be related to your conditions.

Try it with just the >= and then with the <= and see what happens.

Re: qvd file without information

andrey_krylov — Tue, 18 Sep 2018 03:59:17 GMT

Maybe to put apostrophes

where fecha >='$(vFechaInicioCarga)' and fecha <='$(vFechaFinCarga)'

Re: qvd file without information

Anil_Babu_Samineni — Tue, 18 Sep 2018 04:03:42 GMT

Is fecha field is in DD-MM-YYYY this format ??

Re: qvd file without information

simospa — Tue, 18 Sep 2018 14:10:01 GMT

Hi,

here some checks:

1. does it works without this row? where fecha >=$(vFechaInicioCarga) and fecha <=$(vFechaFinCarga);

2. on the title you are saying "qvd without data": before the store you have date in your .qvw ?

3. if the answer to 2. is "yes": did you try to append (qvd) to the store? STORE trx into ..\Qlik\transacciones_$(vPeriodoCarga).qvd (qvd);

Let us know

Re: qvd file without information

ale_nilo84 — Tue, 18 Sep 2018 14:33:37 GMT

just using where fecha >=$(vFechaInicioCarga) save all the information of the query in the qvd

just using where fecha <=$(vFechaFinCarga) does not save anything in the qvd

Re: qvd file without information

ale_nilo84 — Tue, 18 Sep 2018 14:36:02 GMT

using apostrophes the following error appears

ODBC error

Connector reply error: SQL##f - SqlState: 22005, ErrorCode: 4294964266, ErrorMsg: [Microsoft][Controlador ODBC Microsoft Access] No coinciden los tipos de datos en la expresión de criterios.

Re: qvd file without information

ale_nilo84 — Tue, 18 Sep 2018 14:38:32 GMT

Yes, it is the format used in Chile

Re: qvd file without information

Miguel_Angel_Baeyens — Tue, 18 Sep 2018 14:52:35 GMT

If the values stored in the field "fecha" are in the form DD-MM-YYYY and defined as a date field in the RDBMS you will then need to use SQL date functions to compare, or do it in the LOAD part.

If they instead are defined as literals or strings, then you do need to use single or double quotes Chr(39) or Chr(34) respectively, and use the Text() function in your variables, so the value is stored as a string. Else, according to your script $(vFechaInicioCarga) = -2024 (1 - 7 - 2018). This will work though if you use SET instead of LET.

It looks like that WHERE clause is comparing numbers to strings, which happens to be always false, and that's why you get no data or that "fecha" is indeed numeric but the variables are using a different format, like "-2024" instead of "01-07-2018".

Re: qvd file without information

Peter_Cammaert — Tue, 18 Sep 2018 15:02:50 GMT

If I remember correctly, MS Access expects Date values to be formatted as #18/09/2018# (including the hashes)

See: Examples of using dates as criteria in Access queries - Access

Re: qvd file without information

ale_nilo84 — Wed, 19 Sep 2018 00:55:05 GMT

The script doesn't work, please someone can look at the qvw file

The result:

Connecting to MS Access Database;DBQ=C:\Users\Alexis\Desktop\Qlik\base de datos\Transacciones bus y ZP.accdb

Connected

transacciones << 021_Trx_Totales_sin_nulo 0 Lines fetched

Re: qvd file without information

Anil_Babu_Samineni — Wed, 19 Sep 2018 02:26:18 GMT

Can you look variables and share screenshot of those variables calling in text box. Because, The script fine for me

Re: qvd file without information

simospa — Wed, 19 Sep 2018 06:34:07 GMT

Hi,

did you try to apply the suggestione we gave you?

Re: qvd file without information

ale_nilo84 — Wed, 19 Sep 2018 16:16:37 GMT

Yes, I applied Set instead of Let, the following error appears:

ODBC error

Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][Controlador ODBC Microsoft Access] Error de sintaxis en la fecha en la expresión de consulta 'fecha >=date(date#(01072018, 'DDMMYYYY'),'DD-MM-YYYY') and fecha <=date(MonthEnd(vFechaInicioCarga),'DD-MM-YYYY')'.

Then,

Where should I add # characters around dates? In the definition of variables? I'm confused

SET vFechaInicioCarga = date(date#(01072018, 'DDMMYYYY'),'DD-MM-YYYY');

SET vFechaFinCarga = date(MonthEnd(vFechaInicioCarga),'DD-MM-YYYY');

SET vPeriodoCarga = num#(date(vFechaInicioCarga, 'YYYYMM'));

Re: qvd file without information

Peter_Cammaert — Thu, 20 Sep 2018 07:24:05 GMT

A SQL statement is - after all $-sign substitution - sent AS-IS to the external SQL engine (in this case a Jet-engine from MS Access) to be decoded and executed. So the hashes can be added wherever you want. The most simple solution is to add them to the SQL statement itself, like:

SQL SELECT

...

FROM "021_Trx_Totales"

WHERE fecha >= #$(vFechaInicioCarga)# AND fecha <= #$(vFechaFinCarga)#;