Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

REST data additional field from variable

Hello,

I have an issue with creating link between two tables. I am new to Qlick Sense and I cannot find any information on this.

I have two REST endpoints one to get items list and one to get item details.

GET http:/api/items

GET http:/api/items/$(id)/details


I am using this to get the data from the API details:

LET NumRows=NoOfRows('items');

FOR i=1 to $(NumRows)

  Let id = Peek('item_id',$(i),'items');

  timeTmp:

  SQL SELECT

      "field1",

     ....

  FROM JSON (wrap off) "root" PK "field1"

  WITH CONNECTION(Url "http:/api/items/$(id)/details");

next;

And I want each line to have $(id) value in it. How can I achieve this?

I have tried (with no luck):

  timeTmp:

  SQL SELECT

      "field1",

     ....,

      '$(id)' as "item_id",

  FROM JSON (wrap off) "root" PK "field1"

  WITH CONNECTION(Url "http:/api/items/$(id)/details");

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

I have managed to do it by loading inside cycle:

LET NumRows=NoOfRows('issue')-1;

FOR i=0 to $(NumRows)

  Let issue_id = Peek('issueID',$(i),'issue');

  RestConnectorMasterTable:

  SQL SELECT

      "id",

      .....

      "__KEY_root"

  FROM JSON (wrap off) "root" PK "__KEY_root"

  WITH CONNECTION(Url "http:/api/items/$(issue_id)/timetracking/workitem");

  [work_items]:

  LOAD [id] AS [work_item_id],

      .......

      [__KEY_root] AS [work_item_KEY],

      '$(issue_id)'  as [issue_id]

  RESIDENT RestConnectorMasterTable

  WHERE NOT IsNull([__KEY_root]);

  DROP TABLE RestConnectorMasterTable;

next;

View solution in original post

5 Replies
Anonymous
Not applicable
Author

Hello,

Have you found a solution?

I am stuck in a very similar situation.

Anonymous
Not applicable
Author

I have managed to do it by loading inside cycle:

LET NumRows=NoOfRows('issue')-1;

FOR i=0 to $(NumRows)

  Let issue_id = Peek('issueID',$(i),'issue');

  RestConnectorMasterTable:

  SQL SELECT

      "id",

      .....

      "__KEY_root"

  FROM JSON (wrap off) "root" PK "__KEY_root"

  WITH CONNECTION(Url "http:/api/items/$(issue_id)/timetracking/workitem");

  [work_items]:

  LOAD [id] AS [work_item_id],

      .......

      [__KEY_root] AS [work_item_KEY],

      '$(issue_id)'  as [issue_id]

  RESIDENT RestConnectorMasterTable

  WHERE NOT IsNull([__KEY_root]);

  DROP TABLE RestConnectorMasterTable;

next;

Anonymous
Not applicable
Author

Hi,

Yes I do. I have provided example as correct  answer. I hope it will solve your issue.

Anonymous
Not applicable
Author

Thank you very much!

yelda
Contributor
Contributor

I had a similar situation.

Thank you so much for sharing your solution.