topic Re: Joining Tables While Avoiding Synthetic Keys in QlikView

Joining Tables While Avoiding Synthetic Keys

villegasi03 — Thu, 10 Oct 2013 00:38:08 GMT

I have quite a few QVDs that contain employee productivity statistics. My dashboard's audience are managers and everything revolves around the date field and the employee id field. In order to avoid synthetic keys I have been loading each QVD containing some type of employee stats and then concatenating them at the end. As you might know this is creating a ton of null values so I am looking to find more efficient ways of relating the information.

I have experimented with creating a table by performing an outer join on a my employee table and an auto generated calendar but quite frankly I don't know what I am doing. I also did a little poking around and say something that mentioned creating a key by combining the date and employee id field but again I am not sure if that's the best way to go.

Any assistance with this would be very much appreciated.

Thank you

Re: Joining Tables While Avoiding Synthetic Keys

Thu, 10 Oct 2013 02:16:45 GMT

Could you send a model of how the relationship is getting your tables?

Re: Joining Tables While Avoiding Synthetic Keys

villegasi03 — Thu, 10 Oct 2013 02:32:57 GMT

Here is an example of what I have. What I am currently doing is concatenating tables one thru four and then left joing to my calendar table. This would leave me with one big table. So because the data is a mixture of summary (aggregated daily for example, one record for each day for each employee) and some data is details (many records for one employee for each days) I would not be able to left join all the tables. Currently this is causing a great deal of obsolete null values for fields that were not native to the source table.

TABLEONE	TABLETWO	TABLETHREE	TABLEFOUR	CALENDARTABLE
DATE	DATE	DATE	DATE	DATE
EMPLOYEEID	EMPLOYEEID	EMPLOYEEID	EMPLOYEEID	MONTH
CALLSTAKEN	QUALITYAUDITDATE	ACTIVITYTYPE	ADHERENCEPCT	MONTHNAME
TIMEWORKED	QUALITYAUDITTYPE	ACTIVITYDURATION	INADHERENCEPCT	YEAR
	ACTIVITYNAME	OUTADHERENCEPCT	WEEKDAY
		QUARTER

Re: Joining Tables While Avoiding Synthetic Keys

amars — Thu, 10 Oct 2013 03:12:04 GMT

Hi Ismael,

you can load all the tables in this sequence

TABLEONE(DATE1, EMP1, DATE_EMP_KEY, CALLSTAKEN, TIMEWORKED)

TABLETWO(DATE2, EMP2, DATE_EMP_KEY, QUALITYAUDITDATE, QUALITYAUDITTYPE, ACTIVITYNAME)

TABHETHREE(DATE3, EMP3, DATE_EMP_KEY,ACTIVITYTYPE, ACTIVITYDURATION, OUTADHERENCEPCT, QUARTER)

TABLEFOUR(DATE4, EMP4, DATE_EMP_KEY,ADHERENCEPT,......)

Now form a Master table

MASTER_EMP_DATE_TEMP:

Load distinct

DATE1 AS DATE,

EMP1 AS EMP

(DATE1 & EMP1) AS DATE_EMP_KEY

Resident TABLEONE;

CONCATENATE(MASTER_EMP_DATE_TEMP)

TABLETWO...

TABLETHREE...

TABLEFOUR...

MasterEmpDate:

LOAD DISTINCT

DATE,

EMP,

DATE_EMP_KEY

Resident MASTER_EMP_DATE_TEMP;

drop table MASTER_EMP_DATE_TEMP;

Drop FIELD DATE1, EMP1 from TABLEONE;

....TABLETWO;

... TABLETHREE;

...TABLEFOUR;

CALENDARTABLE(DATE, MONTH,.....)

Now all thefour tables should be linked with the DATE_EMP_KEY and the CALENDAR should be linked with DATE.

Thanks...

Re: Joining Tables While Avoiding Synthetic Keys

villegasi03 — Thu, 10 Oct 2013 03:59:21 GMT

It looks like this is going to do the trick. Its going to take me just a bit to set up my script so I should be able to let you know how it turns out in the morning. Thank you very much for your assistance and I will get back as soon as I get set up.

Re: Joining Tables While Avoiding Synthetic Keys

villegasi03 — Fri, 11 Oct 2013 12:21:18 GMT

This worked like a charm. I really appreciate all your assistance.