Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lukas_ignatavic
New Contributor II

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
lukas_ignatavic
New Contributor II

Re: REST data additional field from variable

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;

4 Replies
andreas-hoehn
New Contributor

Re: REST data additional field from variable

Hello,

Have you found a solution?

I am stuck in a very similar situation.

lukas_ignatavic
New Contributor II

Re: REST data additional field from variable

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;

lukas_ignatavic
New Contributor II

Re: REST data additional field from variable

Hi,

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

andreas-hoehn
New Contributor

Re: REST data additional field from variable

Thank you very much!