To join or not to Join

[QFanatic]

hi guys

PFA

The Attached is a very rudimentary explanation of the Scenario I've been handed - just using dummy data so I don't get fired...

I have an Events Table and an Event_Event_Relationship table.

Every event (in this case my birthday 🙂) has 2 'parts'....a Request is sent to people to attend my birthday as well as a Response_received.

What totally confuses me, is the Relationship table...I link Event_id to Event_Id - but I dont know if this is correct?

1. Should I not be linking to BOTH Event_ID and Related_Event_ID?

2. What is the 'use'/potential of the field 'Type_Relation' term..

Possible scenarios:

1. I'd like to count the Event_Name (1 event in this case)

2. I'd like to count the amount of Requests that were sent, as well as the # of Responses_Received.

3. I'd like to identify Requests that were sent, that didnt get any responses ...and Identify these

4. I'd like to identify 'orphan' responses that didnt have requests sent (Just because the data is cr@p and it is possible)

Please may you assist? My brain is foggy and I'm stuck.

Much appreciated.

A

[Marcos_Ferreira_dos_Santos]

Hi QFanatic,

How are you ?  Have you found a satisfying solution for your task ?

Yours is a very interesting question, and may have many different solutions.

Without knowing more details about the project, I could figure out a table like this:

structure:  Event_Name;Request_sent;Request_responded

fullfillment:       a) for sent... Event_Name; Yes; No or Null      b) for responded... Event_Name; No or Null; Yes

It might answer all your 4 scenarios, didn't it ?

Good luck!

Marcos

[QFanatic]

Hello Marcos,
Thank you for your friendly reply.

No I havent been able to solve this, I have left it on the back burner for
a while.

I would love to hear how you got to these 4 scenarios?

[edwin · Accepted solution]

you can de-normalize your first table this way:

events:
load * inline [
Event_ID,Event_Name,Event_Type
1001,Lornas Birthday,Request_sent
1002,Lornas Birthday,Response_received
2001,As Birthday,Request_sent
2002,As Birthday,Response_received
3001,Bs Birthday,Request_sent
3002,Bs Birthday,Response_received
];

relationships:
load * inline [
Event_ID,Related_ID,Type_Relation
1001, 1002,Response_To Request
2001, 2002,Response_To Request
3001, 3002,Response_To Request
];

left join (events)
load Event_ID, Related_ID, Type_Relation
resident relationships;


left join (events)
load Event_ID as Related_ID, Event_Name as ResponseEventName, Event_Type as ResponseEventType
Resident events;


drop table relationships;

this assumes that there is only one response per request  

[edwin]

if the expected responses are multiple you don't want to de-normalize

events:
load * inline [
Event_ID,Event_Name,Event_Type, Responder
1001,Lornas Birthday,Request_sent
1002,Lornas Birthday,Response_received, Person1
5002,Lornas Birthday,Response_received, Person2
6002,Lornas Birthday,Response_received, Person3
7002,Lornas Birthday,Response_received, Person4
2001,As Birthday,Request_sent
2002,As Birthday,Response_received, Person5
3001,Bs Birthday,Request_sent
3002,Bs Birthday,Response_received, Person6
];

relationships:
load * inline [
Event_ID,Related_ID,Type_Relation
1001, 1002,Response_To Request
1001, 5002,Response_To Request
1001, 6002,Response_To Request
1001, 7002,Response_To Request
2001, 2002,Response_To Request
3001, 3002,Response_To Request
];


left join (relationships)
load Event_ID as Related_ID, Event_Name as ResponseEventName,Event_Type as ResponseEventType, Responder
Resident events;

drop field Responder from events;

[QFanatic]

Thank you Marcos,

Every Request should only have one response. Thank you so much for your help.

I appreciate..

Thanks

[QFanatic]

Edwin,

Last thing ...

To determine these criteria with your first solution..please check if I got that right?

1. I'd like to count the Event_Name (1 event in this case). = Count(Distinct Event_name)

2. I'd like to count the amount of Requests that were sent, as well as the # of Responses_Received. = count(Event_type = Request_Type), right?

3. I'd like to identify Requests that were sent, that didnt get any responses ...and Identify these..not sure of this one..

4. I'd like to identify 'orphan' responses that didnt have requests sent (Just because the data is cr@p and it is possible) and this one..

Maybe a good idea is if I rather identify each Event with '1' as IsEvent = then I can just sum them in charts.

1. Where and how would  I do same for Response and Request?

2. And then lastly - same for the 'orphan' scenario.

Thank you!

[edwin]

1. I'd like to count the Event_Name (1 event in this case). = Count(Distinct Event_name)  <EDWIN>if you wish to count distinct names yes, alternatively you  count distinct EVENT IDs where event type = to a request 

2. I'd like to count the amount of Requests that were sent, as well as the # of Responses_Received. = count(Event_type = Request_Type), right? <EDWIN> same comment as above, also to make it easier, you can flag each sent request if it was responded to.  

this is the part that looks up the response:

left join (events)
load Event_ID, Related_ID, Type_Relation, 1 as IsRespondedTo
resident relationships;

its a bit tricky to find those that weren't responded to so if you add this at the end of your script:

rename table events to tmpEvents;
noconcatenate events: load *, if(Event_Type='Request_sent', if(IsRespondedTo=1,1,0) ) as %IsRespondedTo
resident tmpEvents;
drop table tmpEvents;
drop field IsRespondedTo;

so now those with 0 flag didn't receive a response and set analysis is simpler

3. I'd like to identify Requests that were sent, that didnt get any responses ...and Identify these..not sure of this one..  <EDWIN> previous will answer this

4. I'd like to identify 'orphan' responses that didnt have requests sent (Just because the data is cr@p and it is possible) and this one.. <EDWIN>you can implement a similar solution for requests.  left join relationships based on 

left join (events)
load Related_ID as Event_ID, 1 as hasRequest
resident relationships;

then add on to the code in 

noconcatenate events: load *, if(Event_Type='Request_sent', if(IsRespondedTo=1,1,0) ) as %IsRespondedTo, if(Event_Type='Request_received', if(hasRequest=1,1,0) ) as %HasRequest
resident tmpEvents;
drop table tmpEvents;
drop field IsRespondedTo, hasRequest;

[QFanatic]

you are a Rockstar! Thank you!

[edwin]

np