Data Relationship Problem in Qlik Sense with REST API

[Diego_780]

Hello Qlik proffesionals!

I have the next problem:

• I'm consuming data from a REST API that returns a hierarchical JSON structure where each person has an associated array of roles.
• When loading this data into Qlik Sense, it separates the information into two different tables:
  • A person table (Farmpersons_data) with fields like idFinca, idPersona, nombre, email, etc.
  • A roles table (Farmpersons_data_roles) with fields like idRole, nombreRole, nombreInt, etc.
• By separating the data, the original hierarchical relationship that existed in the JSON is lost, where each person had specific assigned roles.
• The main issue is that I need a way to join these two tables to maintain the correct relationship between each person and their corresponding roles, but the automatically generated fields (__KEY_data and __FK_roles) are not correctly preserving this relationship.

Here is an example of what the GET retrieves me (and here, you can clearly see VISUALY the role assigned to each person, VISUALY):

And this is my code:

For Each vCultivoFincaID in FieldValueList('ENLACE')

	Let vURL = '"http://ws.visualnacert.com/vnwebservices/user/$(vUserid)/v3/crops/' & SubField($(vCultivoFincaID), '.', 1) &'/farms/' & SubField($(vCultivoFincaID), '.', 2) & '/farmpersons"';
	
    Trace $(vURL);
    
    RestConnectorMasterTable:
    SQL SELECT 
        "__KEY_root",
        (SELECT 
            "idFinca",
            "idPersona",
            "nombre" AS "nombre_u0",
            "email",
            "telefono",
            "__KEY_data",
            "__FK_data",
            (SELECT 
                "idRole",
                "nombre",
                "nombreInt",
                "__FK_roles"
            FROM "roles" FK "__FK_roles")
        FROM "data" PK "__KEY_data" FK "__FK_data")
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION ( URL $(vURL));

    [Farmpersons_data_roles]:
    LOAD	
    	[idRole],
        [nombre]				AS [nombreRole],
        [nombreInt],
//         SubField($(vCultivoFincaID), '.', 2)		AS idFinca,
        
        [__FK_roles] AS [__KEY_data]
    RESIDENT RestConnectorMasterTable
    WHERE NOT IsNull([__FK_roles]);
	
	
    [Farmpersons_data]:
    LOAD	
    	[idFinca],
        [idPersona],
        [nombre_u0] 			AS [nombre],
        [email],
        [telefono],
        SubField($(vCultivoFincaID), '.', 1)		AS idCultivo,
        
        [__KEY_data]
//         [__FK_data] AS [__KEY_root]
    RESIDENT RestConnectorMasterTable
    WHERE NOT IsNull([__FK_data]);

    DROP TABLE RestConnectorMasterTable;

Next vCultivoFincaID;

How can I join these tables without a common thing??

Is this possible?

Thanks in regards,

Diego.

[henrikalmen]

Isn't __KEY_Data the link between the tables? Or __FK_data that becomes __KEY_root in Farmpersons_data (but you commented that out for some reason). 

[Diego_780]

Hello Henrikalmen,

Yes, __KEY_Data SHOULD be the link that solves me this issue, but as I said before: 

• The main issue is that I need a way to join these two tables to maintain the correct relationship between each person and their corresponding roles, but the automatically generated fields (__KEY_data and __FK_roles) are not correctly preserving this relationship.

Here is an example of what the tables show me with the same filters (idCultivo: 12926 and idFinca: 2):

As you can see, it doesn´t make the correct relation, it should only appear me a few Roles, but instead it shows me ALL the Roles this people have had in ALL the farms and crops. Even if I filter with a person, it still shows me every role it has had historically.

Also I comment __KEY_Root because is irrelevant, it only has a 1 in all values.

Thanks.

[mikaelsc · Accepted solution]

you are looping through all the different farmers, there should be an extra key in your query and resulting tables based on 

vCultivoFincaID 

maybe the api response can do it by itself: 

check the key generation strategy...  

[Diego_780]

Yeah that worked!

I didn´t knew you could change the key generation, now it does make a completly unique key so the relation is established.

Thanks a lot!

Diego.