Hello everyone!
Two days ago, a script that worked without any problem began to fail.
This script uses the Qlik GeoAnalytics Connector with Load Operation to modify the format of a geometry field (WKT to Qlik).
Can anyone help me to solve this problem? May be Patric_Nordstrom?
The following can be seen in the log:
20201203T022149.419-0300 Error: Connector reply error: QVX_SYNTAX_ERROR: Failed to process query: Failed to create dataset GeometriaCables: Data source type "INLINE" not available.
Script:
CALL _AddAppTag('Cables');
IF _ETL = 'metrogestion' THEN
// Máxima cantidad de registros admitidos por el conector 'GeoAnalytics' para la operación Load.
LET vMaxAllowedRows_QGA = 20000;
CALL _LoadMapping('Cables');
CALL _LibConnectTo('MetrogestionDB');
Cables:
NOCONCATENATE
LOAD *,
If(proyectado_cable = 1, Dual('Si', 1), Dual('No', 0)) AS EsCableProyectado,
If(proyectado_cable = 1, RGB(112,48,160), ApplyMap('ColoresCategoriaCableMap', CategoriaCable, RGB(191,191,191))) AS ColorCable,
If(proyectado_cable = 1, 'Proyectado | ') & CodigoCable & ' | ' & TipoCable & ' | ' & CategoriaCable & ' | ' & UsoCable AS EtiquetaCable,
Ceil(RowNo() / $(vMaxAllowedRows_QGA)) AS NumeroCargaTemp
;
SQL SELECT
c.cab_nro AS NumeroCable,
c.cab_cod AS CodigoCable,
c.cab_tipo AS TipoCable,
TRIM(tc.tcab_descrip) AS DescripcionTipoCable,
cc.ccab_descrip AS CategoriaCable,
IFNULL(p.nombre_proveedor, 'No indicado') AS ProveedorIECable,
c.cab_uso AS UsoCable,
IFNULL(tc.pelos, c.cant_pelos) AS CantidadPelosCable,
IFNULL(tc.buffers, 0) AS CantidadBuffersCable,
c.dsec AS SegmentoDesdeCable,
c.hsec AS SegmentoHastaCable,
c.mts AS LongitudCable_mts,
c.lote AS LoteCable,
TRIM(c.cab_descripcion) AS DescripcionCable,
TRIM(c.notas) AS NotasCable,
c.proyectado AS proyectado_cable,
ST_AsText(c.cab_geom) AS geom_cable
FROM
fontime.cables c
INNER JOIN fontime.c_cables cc ON cc.ccab_codigo = c.cab_categ
INNER JOIN fontime.t_cables tc ON tc.tcab_codigo = c.cab_tipo
LEFT JOIN fontime.proveedor_medio p ON p.id_proveedor_medio = c.id_proveedor_medio
WHERE
c.cab_uso <> 'Bajada' AND c.cab_geom IS NOT NULL
;
// Obtener campo de geometría en formato Qlik a través de la operación Load del conector 'GeoAnalytics'.
// Como el conector impone un límite de registros para esta operación, se efectúa una carga segmentada.
LET vNoOfLoads = Ceil(NoOfRows('Cables') / $(vMaxAllowedRows_QGA));
SET vGeoTableName = GeometriaCables;
LET chunkSize = 1000;
$(vGeoTableName)Temp:
NOCONCATENATE
LOAD
Null() AS NumeroCable,
Null() AS GeometriaCable
AUTOGENERATE 0
;
// CALL _LibConnectTo('GeoAnalytics');
CALL _LibConnectTo('QGA');
FOR l = 1 TO $(vNoOfLoads)
$(vGeoTableName):
NOCONCATENATE
LOAD
NumeroCable,
geom_cable
RESIDENT Cables
WHERE
NumeroCargaTemp = $(l)
;
IF FieldNumber('NumeroCable', '$(vGeoTableName)') = 0 THEN
CALL InvalidInlineData('The field NumeroCable in $(vGeoTableName) is not available');
END IF
IF FieldNumber('geom_cable', '$(vGeoTableName)') = 0 THEN
CALL InvalidInlineData('The field geom_cable in $(vGeoTableName) is not available');
END IF
LET $(vGeoTableName)InlineTable = 'NumeroCable' & Chr(9) & 'geom_cable';
LET numRows = NoOfRows('$(vGeoTableName)');
LET chunks = numRows / chunkSize;
FOR n = 0 TO chunks
LET chunkText = '';
LET chunk = n * chunkSize;
FOR i = 0 TO chunkSize - 1
LET row = '';
LET rowNr = chunk + i;
EXIT FOR WHEN rowNr >= numRows;
FOR EACH f IN 'NumeroCable', 'geom_cable'
row = row & Chr(9) & MapSubString('CharToUtfMap', Peek('$(f)', $(rowNr), '$(vGeoTableName)'));
NEXT
chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
NEXT
$(vGeoTableName)InlineTable = $(vGeoTableName)InlineTable & chunkText;
NEXT
CONCATENATE($(vGeoTableName)Temp)
SELECT NumeroCable, geom_cable AS GeometriaCable FROM Load(dataset = '$(vGeoTableName)')
DATASOURCE $(vGeoTableName) INLINE tableName='$(vGeoTableName)', tableFields='NumeroCable,geom_cable', geometryType='WKT', loadDistinct='NO', suffix='', crs='EPSG:5347' {$($(vGeoTableName)InlineTable)}
;
DROP TABLE $(vGeoTableName);
NEXT
// Añadir campo de geometría en formato Qlik.
LEFT JOIN (Cables)
LOAD *
RESIDENT $(vGeoTableName)Temp;
DROP TABLE $(vGeoTableName)Temp;
DROP FIELDS
proyectado_cable, geom_cable, NumeroCargaTemp
FROM Cables;
LET vMaxAllowedRows_QGA =;
LET vNoOfLoads =;
LET $(vGeoTableName)InlineTable =;
LET vGeoTableName =;
LET chunks =;
LET chunk =;
LET chunkSize =;
LET chunkText =;
LET numRows =;
LET row =;
LET rowNr =;
LET l =;
LET n =;
LET i =;
LET f =;
STORE Cables INTO '$(_AppQVDsFolder)/fontime/Cables.qvd';
ELSE
Cables:
NOCONCATENATE
LOAD
$(_AppTableFields)
FROM '$(_AppQVDsFolder)/fontime/Cables.qvd' (qvd)
$(_AppTableFilter);
SET _AppTableFields = *;
LET _AppTableFilter =;
END IF
CALL _Debug('fontime/Cables.qvs');
