topic Re: Creating synthetic key in QlikView

Creating synthetic key

Tue, 15 Sep 2015 19:42:12 GMT

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.

I attached some sample

Thank you in advance!

Re: Creating synthetic key

qlikviewwizard — Wed, 16 Sep 2015 02:16:29 GMT

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;

Re: Creating synthetic key

Wed, 16 Sep 2015 05:08:53 GMT

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.

Thanks!

-Mikael

Re: Creating synthetic key

marcus_sommer — Wed, 16 Sep 2015 05:17:12 GMT

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

- Marcus

Re: Creating synthetic key

balasundaram — Wed, 16 Sep 2015 05:17:52 GMT

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

Re: Creating synthetic key

qlikviewwizard — Wed, 16 Sep 2015 05:23:13 GMT

Hi,

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

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;

Re: Creating synthetic key

sasiparupudi1 — Wed, 16 Sep 2015 07:50:52 GMT

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

];

Re: Creating synthetic key

sasiparupudi1 — Thu, 17 Sep 2015 22:28:57 GMT

Did you manage to solve your problem?

Sasi