Check Connection Reload

[pgalvezt]

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!!

[Nicole-Smith]

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

[Nicole-Smith]

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/ErrorVa...

[pgalvezt]

Im sorry,

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

Thanks!

[Nicole-Smith]

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

[pgalvezt]

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

[Nicole-Smith · Accepted solution]

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