topic Re: To join or not to Join in QlikView

To join or not to Join

QFanatic — Sat, 13 Feb 2021 18:13:13 GMT

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.

Re: To join or not to Join

Marcos_Ferreira_dos_Santos — Fri, 19 Feb 2021 13:25:26 GMT

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

Re: To join or not to Join

QFanatic — Sun, 21 Feb 2021 04:40:38 GMT

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?

Re: To join or not to Join

edwin — Sun, 21 Feb 2021 20:15:01 GMT

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

Re: To join or not to Join

edwin — Sun, 21 Feb 2021 20:16:23 GMT

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;

Re: To join or not to Join

QFanatic — Mon, 22 Feb 2021 06:30:59 GMT

Thank you Marcos,

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

I appreciate..

Thanks

Re: To join or not to Join

QFanatic — Mon, 22 Feb 2021 07:20:57 GMT

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!

Re: To join or not to Join

edwin — Mon, 22 Feb 2021 14:49:59 GMT

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;

Re: To join or not to Join

QFanatic — Mon, 22 Feb 2021 19:35:04 GMT

you are a Rockstar! Thank you!

Re: To join or not to Join

edwin — Mon, 22 Feb 2021 22:44:21 GMT