Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
till_bentz
Contributor III
Contributor III

Incorrect data using QvRestConnector

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;isKeepA...;"

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

Labels (2)
1 Reply
till_bentz
Contributor III
Contributor III
Author

Problem still ongoing... Any ideas would be appreciated