Rest Connector Duplication Error with Auto-Generated keys

[bobiloco]

I am using a REST connector to pull data from an API.

For most nested objects, it creates a __KEY_x value and a __FK_x value, where the FK matches the KEY of the parent object.

This works for all but one nested object where the __KEY_ values are unique. However, I have one nested object where it generates the same value for every parent, making the one object useless.

ie:

Order 1 key = ABC, Nested object FK = ABC and KEY = XYZ

Order 2 key = BCD, Nested object FK = BCD and KEY = XYZ ( used again, but should be different )

Order 3 key = CDE, Nested object FK = CDE and KEY = XYZ ( used again, but should be different )

etc...

I created a new connector to the same API and am still getting the same results. For all other nested objects this works fine! I don't see how I can control this any better or how I can force it to start behaving properly. Please let me know if this has been seen and resolved before! Thank you.

Code:

RestConnectorMasterTable:
SQL SELECT
   "cursor",
   "__KEY_root",
   (SELECT
   "id",
   "__KEY_orders",
   "__FK_orders",
      (SELECT
      "__KEY_additionalInfo",
      "__FK_additionalInfo"
      FROM "additionalInfo" PK "__KEY_additionalInfo" FK "__FK_additionalInfo")
   FROM "orders" PK "__KEY_orders" FK "__FK_orders")
FROM JSON (wrap on) "root" PK "__KEY_root"

...

The __KEY_addtionalInfo is what is consistently showing duplicates.

[rwunderlich]

You can modify the SQL statement to introduce some uniqueness. For example:

Concat( "__KEY_orders", '-', "__KEY_additionalInfo") as "__KEY_additionalInfo",

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[bobiloco]

Thank you for taking a stab at this!

Unfortunately, the point of making this unique is for the nested objects to be able to join back to the parent's key, and the nested objects don't have the __KEY field required to do this, only the FK. I tried adding the KEY field names to the SQL statement but they show up as nulls.

In this example below, all of the __KEY_additionalInfo and __FK_shipCompliant values are exactly the same for every record:

(SELECT
"__KEY_additionalInfo",
"__FK_additionalInfo",
  (SELECT
  "__FK_shipCompliant",
  "shipmentStatus"
  FROM "shipCompliant" FK "__FK_shipCompliant"),

I'll go through our partner to try to raise a ticket for this. I can't see how this is expected behaviour.