topic Rest Connector Duplication Error with Auto-Generated keys in App Development

Rest Connector Duplication Error with Auto-Generated keys

bobiloco — Sat, 16 Nov 2024 16:36:28 GMT

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.

Re: Rest Connector Duplication Error with Auto-Generated keys

rwunderlich — Fri, 14 Jun 2024 00:53:12 GMT

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

Re: Rest Connector Duplication Error with Auto-Generated keys

bobiloco — Fri, 14 Jun 2024 15:11:41 GMT

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.