Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
Could you send a model of how the relationship is getting your tables?
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 |
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...
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.
This worked like a charm. I really appreciate all your assistance.