Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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
Creator
Creator

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

View solution in original post

1 Reply
Creator
Creator

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

View solution in original post