Incorrect data using QvRestConnector

till_bentz — Tue, 21 Dec 2021 20:59:47 GMT

I have a problem using the QlikSense REST Connector trying to access a json webservice (url for testing purposes: https://www.energy-charts.de/price/month_2020_05.json). Im using the generated load script as shown below. I then store the raw data into qvd files (see highlighted section in script code).

Somewhere in the process seems to be an error, because in the qvd some values are wrong.

I am using the following connection string. All sources are public, so feel free to try for yourself.

CUSTOM CONNECT TO "provider=QvRestConnector.exe;url=https://www.energy-charts.de/price/month_2020_05.json;timeout=30;method=GET;httpProtocol=1.1;isKeepAlive=true;bodyEncoding=UTF-8;sendExpect100Continue=true;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=anonymous;skipServerCertificateValidation=false;useCertificate=No;certificateStoreLocation=LocalMachine;certificateStoreName=My;addMissingQueryParametersToFinalRequest=false;PaginationType=None;allowResponseHeaders=false;allowHttpsOnly=false;useProxy=true;proxyAddress=xxxxxxx;proxyPort=xxxxxx;proxyBypassOnLocal=false;proxyUseDefaultCredentials=true;"

Loadscript:

RestConnectorMasterTable:
SQL SELECT
"color",
"chartType",
"xAxisFormat",
"y1AxisDecimalPlaces",
"y2AxisDecimalPlaces",
"datasource",
"date",
"type",
"yAxis",
"disabled",
"__KEY_root",
(SELECT
"en",
"de",
"fr",
"it",
"__FK_key"
FROM "key" FK "__FK_key"),
(SELECT
"en" AS "en_u0",
"de" AS "de_u0",
"fr" AS "fr_u0",
"it" AS "it_u0",
"__FK_chartTitle"
FROM "chartTitle" FK "__FK_chartTitle"),
(SELECT
"en" AS "en_u1",
"de" AS "de_u1",
"fr" AS "fr_u1",
"it" AS "it_u1",
"__FK_xAxisLabel"
FROM "xAxisLabel" FK "__FK_xAxisLabel"),
(SELECT
"en" AS "en_u2",
"de" AS "de_u2",
"fr" AS "fr_u2",
"it" AS "it_u2",
"__FK_y1AxisLabel"
FROM "y1AxisLabel" FK "__FK_y1AxisLabel"),
(SELECT
"en" AS "en_u3",
"de" AS "de_u3",
"fr" AS "fr_u3",
"it" AS "it_u3",
"__FK_y2AxisLabel"
FROM "y2AxisLabel" FK "__FK_y2AxisLabel"),
(SELECT
"__KEY_values",
"__FK_values",
(SELECT
"@Value",
"__FK_values_u0"
FROM "values" FK "__FK_values_u0" ArrayValueAlias "@Value")
FROM "values" PK "__KEY_values" FK "__FK_values")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://www.energy-charts.de/price/year_$(vL.Year).json");

[key]:
LOAD [en],
[de],
[fr],
[it],
[__FK_key] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_key]);
Store key into [lib://QVD_E/$(vL.Year)_key.qvd](qvd);

[chartTitle]:
LOAD [en_u0] AS [en_u0],
[de_u0] AS [de_u0],
[fr_u0] AS [fr_u0],
[it_u0] AS [it_u0],
[__FK_chartTitle] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_chartTitle]);
Store chartTitle into [lib:/QVD_E/$(vL.Year)_chartTitle.qvd](qvd);

[xAxisLabel]:
LOAD [en_u1] AS [en_u1],
[de_u1] AS [de_u1],
[fr_u1] AS [fr_u1],
[it_u1] AS [it_u1],
[__FK_xAxisLabel] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_xAxisLabel]);
Store xAxisLabel into [lib:/QVD_E/$(vL.Year)_xAxisLabel.qvd](qvd);

[y1AxisLabel]:
LOAD [en_u2] AS [en_u2],
[de_u2] AS [de_u2],
[fr_u2] AS [fr_u2],
[it_u2] AS [it_u2],
[__FK_y1AxisLabel] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_y1AxisLabel]);
Store y1AxisLabel into [lib://QVD_E/$(vL.Year)_y1AxisLabel.qvd](qvd);

[y2AxisLabel]:
LOAD [en_u3] AS [en_u3],
[de_u3] AS [de_u3],
[fr_u3] AS [fr_u3],
[it_u3] AS [it_u3],
[__FK_y2AxisLabel] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_y2AxisLabel]);
Store y2AxisLabel into [lib://QVD_E/$(vL.Year)_y2AxisLabel.qvd](qvd);

[values]:
LOAD [@Value],
[__FK_values_u0] AS [__KEY_values]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_values_u0]);
Store values into [lib://QVD_E/$(vL.Year)_values.qvd](qvd);

[values_u0]:
LOAD [__KEY_values],
num([__FK_values]) AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_values]);
Store values_u0 into [lib:/QVD_E/$(vL.Year)_values_u0.qvd](qvd);

[root]:
LOAD [color],
[chartType],
[xAxisFormat],
[y1AxisDecimalPlaces],
[y2AxisDecimalPlaces],
[datasource],
[date],
[type],
[yAxis],
[disabled],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
Store root into [lib://QVD_E/$(vL.Year)_root.qvd](qvd);

Attached you will find two screenshots showing my problem. The first (20200526_JSON_Rawdata.png) is showing the raw data from the json file with several values highlighted.

 [ 1588381200000 , 8 ] , [ 1588384800000 , 8 ] , [ 1588388400000 , 8 ] , [ 1588392000000 , 7.2 ] , [ 1588395600000 , 8 ]

The second screenshot (20200526_QVD_Data.png) show an excerpt from the qvd. As you can see, some values are somehow formatted incorrectly.

1588381200000, 0.008

It dosen't seem to be a general problem with integers as the 10 is formatted correctly.

Does anyone have any idea, what I am doing wrong?

Thanks!

till

Re: Incorrect data using QvRestConnector

till_bentz — Fri, 04 Sep 2020 09:17:28 GMT

Problem still ongoing... Any ideas would be appreciated

topic Incorrect data using QvRestConnector in Connectivity & Data Prep

Incorrect data using QvRestConnector

Re: Incorrect data using QvRestConnector