topic Re: Advice needed for joining tables in QlikView

Advice needed for joining tables

Tue, 20 Sep 2011 16:09:54 GMT

Hi all,

I'm having a spot of bother trying to work out how to join my project related tables in my QVW

I have essentially 3 core tables in QV-talk

PROJECT

TASK_ASSIGNMENT

TEAM

Now, a PROJECT has a TEAM associated with it. This TEAM will be one or more resources.

a PROJECT will also have one or more TASK ASSIGNMENTS

which represent resources assigned to tasks.

The resources on assignments will all be in the TEAM.

however, not all TEAM members will be assigned to tasks.

I need to somehow link the TEAM to the TASK_ASSGINMENT table so that if I pick a resource on the team I can see the Tasks they are assigned to.

I get a synthetic key when I join these together.

I have always thought that synthetic keys are best avoided - is this the case.

I attach a word document showing my structure.

Thanks in advance for any advice...

Advice needed for joining tables

Tue, 20 Sep 2011 16:31:16 GMT

Hi,

in my experience your only one syn is ok, u have to avoid synthetic key if u have a lot of them and the application become heavy and slow.

So when i have from 1 to 3 syn key i do anything.

C u,

Stefano.

Advice needed for joining tables

Tue, 20 Sep 2011 18:12:20 GMT

If there is no other way to combine the tables in your data model to get a big Fact table you can use a link table to avoid synthetic key

for example

Get all the possible list of project ids and resource ids into one table

say the name PrdIdResIDTable

LinkTable:

Load

ProjectId,

ResourceID,

Autonumber(ProjectId &'-'&ResourceID) as LinkKey

Resident PrdIdResIDTable;

Team:

Load

Autonumber(ProjectId &'-'&ResourceID) as LinkKey

.....

(Remember not to include ProjectID and resourceID fields in the individual fact table)

create the other tables with the LinkKey field.

Re: Advice needed for joining tables

Wed, 21 Sep 2011 18:51:37 GMT

THanks for your assistance.

I trid making a link table but it seemed to make the results go a bit funny.

Keeping the Synthetic key seemd to allow it to work as planned.

however I now have a new issue.

Off theeach of the two tables that are causing the synthetic key I have additional tables which contain time-sliceddata from the database, with common fields - thus creating a second synthetic key AARRGHH

I need there to be a single date 'island' that will control each of these two independant slices.

Its causing a circular/loosley coupled reference which is totally ruining the data.

I cant think of a way of doing this.

I know it can be done somehow but havent got a clue.

the first synthetic key works well.

I could possibly change the date field names from the two time-sliced tables (see new attached structure and qvw on original post ), and then link them in a date-island, but I think that would just go and create the same circular reference.

I could combine the time-sliced datainto one table, but some of the time-slices need to link to resource records and some need to link to assignment records.

Can anyone suggest a way of acheiving this?

Thanks in advance.

Matt

Advice needed for joining tables

Wed, 21 Sep 2011 19:22:40 GMT

Hello Matt,

I would load the two Slice-Dates as two Slice_Dates, for ex.:

ACTS_ETCS:

LOAD ASSIGNMENTID,

MONTH(SLICE_DATE) as ASSIGNMENT_MONTH,

YEAR(SLICE_DATE) as ASSIGNMENT_YEAR,

QUARTERNAME(SLICE_DATE) as ASSIGNMENT_QUARTER,

SLICE_DATE as ASSIGNMENT_DATE,

SLICE_TYPE as ASSIGNMENT_TYPE,

HOURS as ASSIGNMENT_HOURS

FROM

Now rename the fields of AVAILABILITY as well. This should bring you out of this mess. 😉

HtH

Roland

Re: Advice needed for joining tables

Wed, 21 Sep 2011 19:33:31 GMT

Thanks for your time Roland.

If I rename the slice attributes, how can I then compare then using one commond date island.

ultimately, I would like to produce bar charts showing total availability, total actuals ans total ETC by week/month etc...

so where a resource exists in the team, but has no assignments, I would see availability values for the weeks/months, but no actuals/etcs (which come from the assignment time-slices)

Re: Advice needed for joining tables

Wed, 21 Sep 2011 22:58:17 GMT

OK,

so I have decided to adjust our way of analysing project/resource info.

I am going to try looking at availability only of those resources who have assignments.

Which means I can link both setsof time slices off the same assignment table (see attached QVW and relationship)

I have been looking at this far too long now and I'm slowly startin to loose any logic when it comes to QlikView scripting - combined with the not having used the tool for several months..

One set of slices links to the assignmentID

One ser of slices links to the resourceID

both have a DATE

I want to be able to plot graphs etc comparing both sets of slices on the same time-scale, as well as have year and month list boxes.

Thinking of a concatenated key of assignment and resource, but not 100% sure how I can combine the slices to get that info

Any further guidance?

Thanks once again...

Matt