Combined key problem in a link table

Wed, 12 Sep 2012 10:34:53 GMT

Hi All,

I have three tabels with the following information

Consultant (consultantid, deskid)-------> consultant-desk (combined key)

Client(clientid, deskid)-------->client-desk(combined key)

timesheet (timesheetid,consultantid, clientid)-------->consultant-client(combined key)

If I join them all, they create a loop. So I created a link table as following

[temp link]:
load consultantid, deskid resident consultant;
join([temp link])
load clientid, deskid resident client;
join([temp link])
load timesheetid,consultantid, clientidresident timesheet;
link:
load consultantid &'-'& deskid as consultant-desk
       clientid &'-'& deskid as client-desk
       consultantid &'-'& clientid as consultant-client
resident 
[temp link];
drop table [temp link];

Now it does not create any loop and it works fine. Now here is another problem. If a client does not have any desk, we cannot join it with timesheet table even though timesheet and client table have only join based on clientid. However, the combination of all three tables have forced the clients to have deskid otherwise, qlikview will not join client and timesheet table.

an example is given below

Client table :

Clientid = 126, deskid = null ------combinedkey(126-)

Consultant table:

consultantid = 113, deskid = 167 ------combinedkey(113-167)

timesheet table:

timsheetid =100 , clientid = 126, consultantid = 113--------combined key (113-126)

[temp link]:
load consultantid, clientid,timesheetid   resident timesheet;

consultant id clientid timesheetid

113 126 100

join([temp link])
load consultantid, deskid resident consultant;

consultant id clientid deskid timesheetid

113 126 167 100

join([temp link])
load clientid, deskid resident client;

consultant id clientid deskid timesheetid

113 126 167 100

126 -

as you can see, in the link table, clientid 126 stands seperate from others. Similar can be case with consultant table, if it has a null desk and client has a desk assigned and both consultant and client has combined entry in timesheet .

What are the possibel solutions for this. In a simple scenario, I would join client table with timesheet based on clientid and with consultant table based on deskid. In the link table, I am joining client table with link table on both clientid and desk id, which is creating problems for me

Regards

Arif

topic Combined key problem in a link table in QlikView

Combined key problem in a link table