I'm using QvRestConnector to connect to an API that returns JSON data. In "root" there is a very important field called articleId. A short example with just two records is this:
[
{"articleId":"1.654030","shareCounters":{"facebook":7,"googleplus":0,"linkedin":20,"total":27},"shareData":{"lastModified":"2016-11-17T13:25:50","articleUrl":"http%3A%2F%2Fcomputersweden.idg.se%2F2.2683%2F1.654030%2Fklicka-webben","facebookLastModified":"2016-11-17T14:07:24"}},
{"articleId":"1.65403","shareCounters":{"facebook":1,"googleplus":0,"linkedin":0,"total":1},"shareData":{"lastModified":"2016-11-17T13:25:02","articleUrl":"http%3A%2F%2Fpcforalla.idg.se%2F2.1054%2F1.65403","facebookLastModified":"2016-11-17T14:07:18"}}
]
When I load these two articles into QV using QvRestConnector I want the result to be a table with two rows, with articleId as key. My problem is that articleId isn't being treated correctly. The result, when using this example response, is always that I end up with a table with two rows where both rows have the same articleId (either 1.654030 or 1.65403 depending on connection options).
I tried the four different options for "Key Generation Strategy" in QvRestConnector connection settings. They give different results, and I don't understand why. None of them gives correct result though:
- Sequence ID: 1.654030
- Current record: 1.65403
- Fully qualified record: 1.654030
- No keys: 1.654030
Three of them keeps the ending zero digit, but "forgets" about the articleId that actually does not have an ending zero. And one of them discards the trailing zero in articleId.
Thegenerated restconnector script starts like this:
RestConnectorMasterTable:
SQL SELECT
"articleId",
I've tried modifying this "SQL" (but isn't it fake SQL since there's no SQL database recieving the query?) and this is what I've tried so far but nothing works:
- text("articleId) as "articleId"
- replace("articleId", 0, 'x') as "articleId
- cast("articleId" as char(10)) as "articleId"
- cast("articleId" as varchar(10)) as "articleId"
- convert(varchar(10), "articleId") as "articleId"
- convert(char(10), "articleId") as "articleId"
I suppose these commands won't work since they are aimed at QvRestConnector rather than a SQL server or QV itself, but what is it that I haven't yet tried that will solve my problem?
I also tried LOAD text([articleId]) as articleId resident RestConnectorMasterTable but it doesn't help either because it's too late - the damage is already done and all this gives is that the field that already contains an error is converted to a string.
How do I solve this, can anyone help..?
