Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

henrikalmen
Contributor

Data type problem with JSON and QvRestConnector

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..?

1 Solution

Accepted Solutions
henrikalmen
Contributor

Re: Data type problem with JSON and QvRestConnector

I solved it myself today. Preceding load works on the SQL SELECT statement. Transforming using text(articleId) works there.

1 Reply
henrikalmen
Contributor

Re: Data type problem with JSON and QvRestConnector

I solved it myself today. Preceding load works on the SQL SELECT statement. Transforming using text(articleId) works there.

Community Browser