Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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);
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
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...
Im sorry,
I didn't Understand that, can you please attach some example?
Thanks!
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
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);
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);