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
