Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ale_nilo84
Contributor
Contributor

qvd file without information

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;

14 Replies
ale_nilo84
Contributor
Contributor
Author

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

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
simospa
Partner
Partner

Hi,

did you try to apply the suggestione we gave you?

S.

ale_nilo84
Contributor
Contributor
Author

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'));



Peter_Cammaert

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)#;

View solution in original post