How to solve Synthetic key in this case?

[RoyBatty]

Hi,

We load data from the service we use via the REST connector. Below is a simplified script that represents the data structure (entities and keys) we got:

[tickets]:
Load 
   tickets.id,
   tickets.requester_id as contacts.id,
   tickets.responder_id as agents.id
   // Other fields are omitted for simplicity
Inline [
   tickets.id, tickets.requester_id, tickets.responder_id
   1, 2, 3
   2, 3, 1
   3, 3, 2
   4, 1, 2 
];

[conversations]:
Load 
   conversations.id,
   conversations.ticket_id as tickets.id,
   conversations.contact_id as contacts.id, // If contact_id is set, then agent_id will be empty (conversation can belong to either contact or agent)
   conversations.agent_id as agents.id // If agent_id is set, then agent_id will be empty (conversation can belong to either contact or agent)
   // Other fields are omitted for simplicity
Inline [
   conversations.id, conversations.ticket_id, conversations.contact_id, conversations.agent_id
   1, 1, 2, 2
   2, 1, 1, 2
   3, 2, 3, 1
   4, 4, 2, 3 
];

[contacts]:
Load 
   contacts.id
   // Other fields are omitted for simplicity
Inline [
   contacts.id
   1,
   2,
   3 
];

[agents]:
Load 
   agents.id
   // Other fields are omitted for simplicity
Inline [
   agents.id
   1,
   2,
   3 
];

This results in a Synthetic key:

$Syn 1 = tickets.id+contacts.id+agents.id

We have seen the Qlik documentation on synthetic keys, where it states that renaming one or more fields in the tables is a solution, but we are not sure that this is possible in this case because there are the following requirements:

--------------------------------------------------------------------------------------------------------

conversations entity:

• Note: conversation can belong to either contact or agent. If contact_id is set, then agent_id will be empty and vice versa. So, the owner can be either a contact or an agent, but not both at the same time. Also, each conversation will always belong to one ticket
• For each conversation, show the ticket to which it belongs as well as the owner (contact or agent);

tickets entity:

• For specific ticket(s), display conversations (a ticket can have 0 or more conversations);
• For each ticket, display the contact and the agent who owns it (each ticket belongs to one contact and one agent)

contacts entity:

• For specific contact(s), display tickets (a contact can have 0 or more tickets);
• For specific contact(s), display conversations (a contact can have 0 or more conversations);

agents entity:

• For specific agent(s), display tickets (an agent can have 0 or more tickets);
• For specific agent(s), display conversations (an agent can have 0 or more conversations);

--------------------------------------------------------------------------------------------------------

It seems that if we rename/remove one or more fields from any entity, then we will lose the relationship/ability to implement at least one of the above requirements? 🤔

Do you have any suggestions on how we can resolve this?

[marksouzacosta · Accepted solution]

Hi @RoyBatty ,

What about this:

[tickets]:
Load 
   tickets.id,
   tickets.requester_id as requester_id,
   tickets.responder_id as responder_id
Inline [
   tickets.id, tickets.requester_id, tickets.responder_id
   1, 2, 3
   2, 3, 1
   3, 3, 2
   4, 1, 2 
];


[Requesters]:
Load 
   contacts.id as requester_id
   // Other fields are omitted for simplicity
Inline [
   contacts.id
   1,
   2,
   3 
];

[Responders]:
Load 
   agents.id as responder_id
   // Other fields are omitted for simplicity
Inline [
   agents.id
   1,
   2,
   3 
];




[conversations]:
Load 
   conversations.id,
   conversations.ticket_id as tickets.id,
   conversations.contact_id as contacts.id, // If contact_id is set, then agent_id will be empty (conversation can belong to either contact or agent)
   conversations.agent_id as agents.id // If agent_id is set, then agent_id will be empty (conversation can belong to either contact or agent)
   // Other fields are omitted for simplicity
Inline [
   conversations.id, conversations.ticket_id, conversations.contact_id, conversations.agent_id
   1, 1, 2, 2
   2, 1, 1, 2
   3, 2, 3, 1
   4, 4, 2, 3 
];

[contacts]:
Load 
   contacts.id
   // Other fields are omitted for simplicity
Inline [
   contacts.id
   1,
   2,
   3 
];

[agents]:
Load 
   agents.id
   // Other fields are omitted for simplicity
Inline [
   agents.id
   1,
   2,
   3 
];

[RoyBatty]

So basically, create a duplicate tables:

• [requesters] table is the same as [contacts] table (just different table and column names)
• [responders] table is the same as [agents] table (just different table and column names)

We are not sure if this is the best solution because:

• We duplicated the tables, and it can be confusing: both represent the "agent", both are exactly identical, but they are unrelated and if the user filters by a value from one of those two tables - then it will not filter by the same value from the other table. So, filtering by "John Smith" from the [agents] table is not the same (will not give the same results) as if we were to filter by the same "John Smith" from the [responders] table.

Actually, we are not sure, maybe no other/better way is possible to handle this. Anyway, we'll give it some thought, test it, and see how it goes... and come back here to conclude 🙂 Thank you!

[marksouzacosta]

That is the beauty of Qlik. In Global Search Bar, if you type for John Smith, it will show up on both Agents and Responders fields. So, separated tables/fields are classifying in advance the role of the agent in different contexts. This will be specially interesting if you use Left Keep to reduce the agent source table by the context (agent or responders) so you can have a complete list of Agents or Responders.

It is possible to keep Agents and Responders in one single table but his is way trickier and I'm not sure if this will improve the usability of the app and the craft of the Expressions. Maybe joining the Conversations to the Tickets folder + multiplying records on Agents and Contacts tables + some field manipulation will do the magic.

[RoyBatty]

I would add that, in addition to the solution that @marksouzacosta  offered, another option is to leave the synthetic key:

1. If the data model is correct
2. and if there's only one synthetic key
3. and $Syn 1 Table is relatively small (in our case less than 34000 rows)

then there should be no problem handling it, nor should there be any problem with excessive amount of time and/or memory.

--------------------------------------------------

UPDATE:

One more note: In case you decide to keep synthetic key, use separate keys for relations. In our example (given above), the following synthetic key is formed:

$Syn 1 = tickets.id+contacts.id+agents.id

So we will add keys in all those tables: %ticket_id, %contact_id and %agent_id:

[tickets]:
Load 
   tickets.id,
   tickets.id as %ticket_id, // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   tickets.requester_id as %contact_id, // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   tickets.responder_id as %agent_id // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   // Other fields are omitted for simplicity
Inline [
   tickets.id, tickets.requester_id, tickets.responder_id
   1, 2, 3
   2, 3, 1
   3, 3, 2
   4, 1, 2 
];

[conversations]:
Load 
   conversations.id,
   conversations.ticket_id as %ticket_id, // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   conversations.contact_id as %contact_id, // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   conversations.agent_id as %agent_id // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   // Other fields are omitted for simplicity
Inline [
   conversations.id, conversations.ticket_id, conversations.contact_id, conversations.agent_id
   1, 1, 2, 2
   2, 1, 1, 2
   3, 2, 3, 1
   4, 4, 2, 3 
];

[contacts]:
Load 
   contacts.id,
   contacts.id as %contact_id // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   // Other fields are omitted for simplicity
Inline [
   contacts.id
   1,
   2,
   3 
];

[agents]:
Load 
   agents.id,
   agents.id as %agent_id // <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
   // Other fields are omitted for simplicity
Inline [
   agents.id
   1,
   2,
   3 
];

Now the synthetic key will look like this:

$Syn 1 = %ticket_id+%contact_id+%agent_id

So, keys %ticket_id, %contact_id and %agent_id will be used only for relationships and nothing more. They should not be used as dimensions or for measurements on visualizations. For example, you would use tickets.id as the dimension (not %ticket_id). Or, if you want to count tickets: Count([tickets.id]) (not Count([%ticket_id])).