topic Re: Check Connection Reload in QlikView

Check Connection Reload

pgalvezt — Fri, 01 Jun 2018 16:08:55 GMT

Hello,

I have a QlikView load model that contains 8 Sheets (8 Queries),
Each Sheet has 4 different connections (4 countries) but it is the same query for the 4 Countries and everything is concatenated.
at the end of the 4 connections the Query is saved on a qvd.

To know if a query falls because of bad connectivity to the database, I have to
check the "log" of the day and check errors.

Is it possible to leave it automatic? that is, once the load model execution is finished with
these 8 queries, and for example a query has fallen because the connection just fell off in that minute.
I would like to retrieve that query automatically, is it possible?

The idea is to check the whole process and if any query fell back to execute it, but only that query and not the whole model again

Thanks!!

Re: Check Connection Reload

Nicole-Smith — Fri, 01 Jun 2018 16:47:39 GMT

You could put each query in its own loop:

Set success=0;

Do while success=0

[enter your load statement here]

Let success=[enter a calculation to change the 0 to a 1 if it is successful];

Loop

Re: Check Connection Reload

Nicole-Smith — Fri, 01 Jun 2018 16:51:29 GMT

I'll also add that you may need to change your ErrorMode from 1 to 0 if you're not doing this already. More information here: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/ErrorVariables/ErrorVariables.htm

Re: Check Connection Reload

pgalvezt — Fri, 01 Jun 2018 21:00:39 GMT

Im sorry,

I didn't Understand that, can you please attach some example?

Thanks!

Re: Check Connection Reload

Nicole-Smith — Mon, 04 Jun 2018 14:28:58 GMT

Please post an example with your code, and I can help you fit my logic around it.

Preparing examples for Upload - Reduction and Data Scrambling

Re: Check Connection Reload

pgalvezt — Mon, 04 Jun 2018 16:42:32 GMT

Hi Nicol,

Here is my example. I have changed the name of the field of course. and this is the same for the others countries. We are talking the same Tables, Same Fields But distinct Countries (Different ODBC Connections)... Country1,Country2.Country3 ETC.

/////First Sheet

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

Sales_Pais1:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6

'Pais1' as Pais;

SQL SELECT

A.Field1,

A.Field2,

A.Field3,

A.Field4,

A.Field5,

SUM(B.Field6) AS 'Field6'

FROM "DebitCard_Data".dbo."HP_VENTA" AS A WITH (NOLOCK)

INNER JOIN "Data".dbo."NAME_TABLE1" AS B WITH (NOLOCK) ON A.Field1 = B.Field1

WHERE convert(varchar(8), FechaVenta, 112) = convert(varchar(8), dateadd(day,datediff(day,1,GETDATE()),0), 112)

GROUP BY A.Field1,A.Field2, A.Field3, A.Caja, A.Field4, A.Field5;

STORE Sales_Pais1 Into $(Repositorio_QVD)NAME_QVD $(FiltroQVDFecha_da).qvd(qvd);

Drop Table Sales_Pais1;

/////Second Sheet

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

GJH_Pais1:

LOAD

Field1,

Field2,

Field3,

Field4,

'Pais1' as Pais;

SQL SELECT

Field1,

Field2,

Field3,

Field4

FROM "Data".dbo."NAME_TABLE2"

WHERE cast(FechayHora as varchar(10)) = convert(varchar(10), dateadd(day,datediff(day,1,GETDATE()),0), 120);

STORE GJH_Pais1 Into $(Repositorio_QVD)NAME_QVD2 $(FiltroQVDFecha_da).qvd(qvd);

/////Third Sheet

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

GJHHHP_Pais1:

LOAD

Field1,

Field2,

'Pais1' as Pais;

SQL SELECT

Field1,

Field2

FROM "Data".dbo."NAME_TABLE3"

WHERE cast(FechayHora as varchar(10)) = convert(varchar(10), dateadd(day,datediff(day,1,GETDATE()),0), 120);

STORE GJHHHP_Pais1: Into $(Repositorio_QVD)NAME_QVD3 $(FiltroQVDFecha_da).qvd(qvd);

Re: Check Connection Reload

Nicole-Smith — Mon, 04 Jun 2018 20:21:21 GMT

The red lines below are what I would add to your code (you may need to adjust--but they should work close to as-is):

/////First Sheet

SET ErrorMode=0;

SET Success=0;

DO WHILE Success=0

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

Sales_Pais1:

LOAD

Field1,

Field2,

Field3,

Field4,

Field5,

Field6

'Pais1' as Pais;

SQL SELECT

A.Field1,

A.Field2,

A.Field3,

A.Field4,

A.Field5,

SUM(B.Field6) AS 'Field6'

FROM "DebitCard_Data".dbo."HP_VENTA" AS A WITH (NOLOCK)

INNER JOIN "Data".dbo."NAME_TABLE1" AS B WITH (NOLOCK) ON A.Field1 = B.Field1

WHERE convert(varchar(8), FechaVenta, 112) = convert(varchar(8), dateadd(day,datediff(day,1,GETDATE()),0), 112)

GROUP BY A.Field1,A.Field2, A.Field3, A.Caja, A.Field4, A.Field5;

IF ScriptError=0 THEN

SET Success=1;

SET ErrorMode=1;

ENDIF

LOOP

STORE Sales_Pais1 Into $(Repositorio_QVD)NAME_QVD $(FiltroQVDFecha_da).qvd(qvd);

Drop Table Sales_Pais1;

/////Second Sheet

SET ErrorMode=0;

SET Success=0;

DO WHILE Success=0

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

GJH_Pais1:

LOAD

Field1,

Field2,

Field3,

Field4,

'Pais1' as Pais;

SQL SELECT

Field1,

Field2,

Field3,

Field4

FROM "Data".dbo."NAME_TABLE2"

WHERE cast(FechayHora as varchar(10)) = convert(varchar(10), dateadd(day,datediff(day,1,GETDATE()),0), 120);

IF ScriptError=0 THEN

SET Success=1;

SET ErrorMode=1;

ENDIF

LOOP

STORE GJH_Pais1 Into $(Repositorio_QVD)NAME_QVD2 $(FiltroQVDFecha_da).qvd(qvd);

/////Third Sheet

SET ErrorMode=0;

SET Success=0;

DO WHILE Success=0

ODBC CONNECT32 TO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; //Pais1

GJHHHP_Pais1:

LOAD

Field1,

Field2,

'Pais1' as Pais;

SQL SELECT

Field1,

Field2

FROM "Data".dbo."NAME_TABLE3"

WHERE cast(FechayHora as varchar(10)) = convert(varchar(10), dateadd(day,datediff(day,1,GETDATE()),0), 120);

IF ScriptError=0 THEN

SET Success=1;

SET ErrorMode=1;

ENDIF

LOOP

STORE GJHHHP_Pais1: Into $(Repositorio_QVD)NAME_QVD3 $(FiltroQVDFecha_da).qvd(qvd);