Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating synthetic key

Hi experts!

I'm having a problem with a project where I have multiple tables which have a lot of linking fields. I have searched about synthetic keys but I just can't figure out how to use those keys in this case. I hope you can help me.

I have 5 tables: Employees, Absences, Invoices, Salaries and Cost Centre Stucture.

My biggest problem is the cost centre structure which connects employees and invoices.

Capture.PNG

I attached some sample

Thank you in advance!

7 Replies
qlikviewwizard
Master II
Master II

Hi Check this:

Employees:

Load *,EmployeeID&Year&Month as Key,Year&Month as Key1;

LOAD EmployeeID, Department1, Month, Year INLINE [

    EmployeeID, Department1, Year, Month

    1, 1, 2015, 1

    1, 1, 2015, 2

    1, 2, 2015, 3

    2, 2, 2015, 1

    2, 3, 2015, 2

    2, 3, 2015, 3

];

Absences:

Load *,EmployeeID&Year&Month as Key;

LOAD  EmployeeID, [Days asence], Month, Year INLINE [

    EmployeeID, Days asence, Month, Year

    1, 2, 1, 2015

    1, 1, 3, 2015

];

Invoices:

Load *, Year&Month as Key1;

LOAD InvoiceID, [Cost Centre], Month, Year, € INLINE [

    InvoiceID, Cost Centre, Month, Year, €

    1, 1, 1, 2015, 500

    2, 1, 1, 2015, 1000

    3, 2, 2, 2015, 1500

    3, 3, 2, 2015, 500

    4, 4, 2, 2015, 100

    5, 5, 3, 2015, 500

    6, 5, 3, 2015, 400

    7, 3, 3, 2015, 500

    8, 4, 3, 2015, 500

];

Salaries:

Load *,EmployeeID&Year&Month as Key;

LOAD EmployeeID, Month, Year, € as Salary_€ INLINE [

    EmployeeID, Month, Year, €

    1, 1, 2015, 2000

    1, 2, 2015, 2000

    1, 3, 2015, 2000

    2, 1, 2015, 3000

    2, 2, 2015, 3000

    2, 3, 2015, 3000

];

Cost_Centre_Structure:

LOAD [Cost Centre], [Cost Centre 2], Department, [Cost Centre 3], Year INLINE [

    Cost Centre, Cost Centre 2, Department, Cost Centre 3, Year

    1, 1, 1, 1, 2015

    2, 2, 1, 1, 2015

    3, 3, 1, 1, 2015

    4, 1, 1, 1, 2015

    5, 2, 1, 1, 2015

    1, 3, 2, 1, 2015

    2, 1, 2, 1, 2015

    3, 2, 2, 1, 2015

    4, 3, 2, 1, 2015

    5, 1, 2, 1, 2015

];

drop Fields Month, Year from Employees;

drop Fields Month, Year,EmployeeID from Salaries;

drop Fields Month, Year,EmployeeID from Absences;

drop Fields  Year from Cost_Centre_Structure;

export.png

Not applicable
Author

Thank you for you quick answer! I just found out that I had a small mistake in my example. Here's a right now.

I would prob need a master calendar too?

The problem is this:

I have a cost structure which has 5 levels.I can connect invoices to the "deepest" level which is cost centre. I can also connect employees to this structure but I can connect that only on the Cost structure 3 level. Every month I get new information about the employees, invoices, etc. Cost structure updates every year.

Capture.PNG

Thanks!

-Mikael

marcus_sommer

Have a look on the first 3 links in this postings: Get started with developing qlik datamodels.

- Marcus

balasundaram
Creator II
Creator II

Hi,

If we have multiple linking fields, we have to do link table.By using link table we can do data model without synthetic keys.

For reference find attached file.

Regards,

bala

qlikviewwizard
Master II
Master II

Hi,

Check this. Hope this is what you are looking for.

Capture.PNG

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='D.M.YYYY';

SET TimestampFormat='D.M.YYYY h:mm:ss[.fff]';

SET MonthNames='tammi;helmi;maalis;huhti;touko;kesä;heinä;elo;syys;loka;marras;joulu';

SET DayNames='ma;ti;ke;to;pe;la;su';

Employees:

Load *,EmployeeID&Year&Month as Key,Year&Month as Key1;

LOAD EmployeeID, Department1, Month, Year INLINE [

    EmployeeID, Department1, Year, Month

    1, 1, 2015, 1

    1, 1, 2015, 2

    1, 2, 2015, 3

    2, 2, 2015, 1

    2, 3, 2015, 2

    2, 3, 2015, 3

];

Absences:

Load *,EmployeeID&Year&Month as Key;

LOAD  EmployeeID, [Days asence], Month, Year INLINE [

    EmployeeID, Days asence, Month, Year

    1, 2, 1, 2015

    1, 1, 3, 2015

];

Invoices:

Load *, Year&Month as Key1;

LOAD InvoiceID, [Cost Centre], Month, Year, € INLINE [

    InvoiceID, Cost Centre, Month, Year, €

    1, 1, 1, 2015, 500

    2, 1, 1, 2015, 1000

    3, 2, 2, 2015, 1500

    3, 3, 2, 2015, 500

    4, 4, 2, 2015, 100

    5, 5, 3, 2015, 500

    6, 5, 3, 2015, 400

    7, 3, 3, 2015, 500

    8, 4, 3, 2015, 500

];

Salaries:

Load *,EmployeeID&Year&Month as Key;

LOAD EmployeeID, Month, Year, € as Salary_€ INLINE [

    EmployeeID, Month, Year, €

    1, 1, 2015, 2000

    1, 2, 2015, 2000

    1, 3, 2015, 2000

    2, 1, 2015, 3000

    2, 2, 2015, 3000

    2, 3, 2015, 3000

];

Cost_Centre_Structure:

LOAD [Cost Centre], [Cost Centre 2], Department, [Cost Centre 3], Year INLINE [

    Cost Centre, Cost Centre 2, Department, Cost Centre 3, Year

    1, 1, 1, 1, 2015

    2, 2, 1, 1, 2015

    3, 3, 1, 1, 2015

    4, 1, 1, 1, 2015

    5, 2, 1, 1, 2015

    1, 3, 2, 1, 2015

    2, 1, 2, 1, 2015

    3, 2, 2, 1, 2015

    4, 3, 2, 1, 2015

    5, 1, 2, 1, 2015

];

LET vDateMin=num(makedate(2014,01,01));

LET vDateMax=floor(monthend(today()));

LET vDateToday = num(today());

Calender:

Load *, Year&Month as Key1;

LOAD Distinct Date($(vDateMin) + RowNo() - 1) AS Date,

month(Date($(vDateMin) + RowNo() - 1)) AS Month,

year(Date($(vDateMin) + RowNo() - 1)) AS Year,

  monthname(Date($(vDateMin) + RowNo() - 1)) AS Monthname,

  Week(Date($(vDateMin) + RowNo() - 1)) AS Week

AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

drop Fields Month, Year from Employees;

drop Fields Month, Year,EmployeeID from Salaries;

drop Fields Month, Year,EmployeeID from Absences;

drop Fields  Year from Cost_Centre_Structure;

drop Fields Month, Year from Invoices;

sasiparupudi1
Master III
Master III

QUALIFY Year, Month,€;

Employees:

LOAD * INLINE [

    EmployeeID, Department1, Year, Month

    1, 1, 2015, 1

    1, 1, 2015, 2

    1, 2, 2015, 3

    2, 2, 2015, 1

    2, 3, 2015, 2

    2, 3, 2015, 3

];

Absences:

LOAD * INLINE [

    EmployeeID, Days asence, Month, Year

    1, 2, 1, 2015

    1, 1, 3, 2015

];

Invoices:

LOAD * INLINE [

    InvoiceID, Cost Centre, Month, Year, €

    1, 1, 1, 2015, 500

    2, 1, 1, 2015, 1000

    3, 2, 2, 2015, 1500

    3, 3, 2, 2015, 500

    4, 4, 2, 2015, 100

    5, 5, 3, 2015, 500

    6, 5, 3, 2015, 400

    7, 3, 3, 2015, 500

    8, 4, 3, 2015, 500

];

Salaries:

LOAD * INLINE [

    EmployeeID, Month, Year, €

    1, 1, 2015, 2000

    1, 2, 2015, 2000

    1, 3, 2015, 2000

    2, 1, 2015, 3000

    2, 2, 2015, 3000

    2, 3, 2015, 3000

];

Cost_Centre_Structure:

LOAD * INLINE [

    Cost Centre, Cost Centre 2, Department1, Cost Centre 3, Year

    1, 1, 1, 1, 2015

    2, 2, 1, 1, 2015

    3, 3, 1, 1, 2015

    4, 1, 1, 1, 2015

    5, 2, 1, 1, 2015

    1, 3, 2, 1, 2015

    2, 1, 2, 1, 2015

    3, 2, 2, 1, 2015

    4, 3, 2, 1, 2015

    5, 1, 2, 1, 2015

];

Untitled.jpg

sasiparupudi1
Master III
Master III

Hi

Did you manage to solve your problem?

Sasi