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
