Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Certification Exam Sale - 40% off. Register by June 30, 2020. GET DETAILS
Contributor

Joining Tables While Avoiding Synthetic Keys

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

1 Solution

Accepted Solutions
Highlighted
Valued Contributor

Re: Joining Tables While Avoiding Synthetic Keys

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...


View solution in original post

5 Replies
Highlighted
Not applicable

Re: Joining Tables While Avoiding Synthetic Keys

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

Highlighted
Contributor

Re: Joining Tables While Avoiding Synthetic Keys

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
Highlighted
Valued Contributor

Re: Joining Tables While Avoiding Synthetic Keys

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...


View solution in original post

Highlighted
Contributor

Re: Joining Tables While Avoiding Synthetic Keys

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.

Highlighted
Contributor

Re: Joining Tables While Avoiding Synthetic Keys

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