topic Creating Circular reference in QlikView

Creating Circular reference

Tue, 05 Jan 2016 05:26:38 GMT

Hi All,

I have a data model situation that I wanted to try out in QlikView and play with different options with Circular reference. Below is the data model that I am trying to build.

I am using the LOAD INLINE to build these tables as:

Employee:

LOAD * INLINE [

EmpId, EmpName, Title, Salary, CityId, DeptId

101, John Smith, Sales Mgr, 110000, 4, 1

201, Sara Ramirez, Sales Associate, 58000, 5, 1

213, Samuel Baker, IT Engineer 1, 63500, 1, 2

211, David Mcginti, IT Director, 145000, 2, 2

256, Alan Card, Business Mgr, 133000, 3, 3

];

Department:

LOAD * INLINE [

DeptId, DeptName, CityId

1, Sales, 1

2, IT, 2

3, Marketing, 3

];

City:

LOAD * INLINE [

CityId, CityName

1, Columbus

2, Cleveland

3, Akron

4, Dublin

5, Hilliard

6, Powell

7, Grove City

];

Problem:

The problem that I am running into is that QlikView creates a Synthetic key to join the Employee and Department tables. If I rename the City field in the Employee and Department tables, it will not join join it to the City table.

How can I get the joins to work as:

Employee.DeptId = Department.DeptId AND

Employee.CityId = City.CityId AND

Department.CityId = City.CityId

Re: Circular reference

abirami_palanis — Tue, 05 Jan 2016 09:10:53 GMT

Hi,

You have three tables .There is no possibility for Three joins. The rule is, if you have N tables ,only (N-1) joins are possible.

you can rename department table cityId . Then you can get data model like below.

these links are enough for association.

Employee.DeptId = Department.DeptId AND

Employee.CityId = City.CityId AND

Re: Circular reference

arulsettu — Tue, 05 Jan 2016 09:14:21 GMT

may be this

QUALIFY *;

UNQUALIFY CityId;

Employee:

LOAD * INLINE [

EmpId, EmpName, Title, Salary, CityId, DeptId

101, John Smith, Sales Mgr, 110000, 4, 1

201, Sara Ramirez, Sales Associate, 58000, 5, 1

213, Samuel Baker, IT Engineer 1, 63500, 1, 2

211, David Mcginti, IT Director, 145000, 2, 2

256, Alan Card, Business Mgr, 133000, 3, 3

];

Department:

LOAD * INLINE [

DeptId, DeptName, CityId

1, Sales, 1

2, IT, 2

3, Marketing, 3

];

City:

LOAD * INLINE [

CityId, CityName

1, Columbus

2, Cleveland

3, Akron

4, Dublin

5, Hilliard

6, Powell

7, Grove City

];

Re: Circular reference

tyagishaila — Tue, 05 Jan 2016 11:06:29 GMT

i think

these two links are enough for association

Employee.DeptId = Department.DeptId AND

Employee.CityId = City.CityId AND

you can rename CityId of Department table.

Re: Circular reference

tyagishaila — Tue, 05 Jan 2016 11:17:12 GMT

to remove circular reference, you can concatenate these tables.

Re: Circular reference

Anonymous — Tue, 05 Jan 2016 12:35:19 GMT

Qlikview works best with flat tables rather than normalized. If at all possible, I would just left join the city table into the employee table in the load script. Depending on the number of records, I probably would left join the dept table as well.

Re: Circular reference

PrashantSangle — Tue, 05 Jan 2016 12:38:33 GMT

Hi,

use link table concept.

Regards,

Re: Circular reference

Peter_Cammaert — Tue, 05 Jan 2016 12:53:55 GMT

Since the synthetic key has nothing to do with the Circular Reference, and you want to keep the Circular Reference to experiment with, remove the synthetic key by creating a composite key in Employee and Department that consists of CityId and DeptId. Drop those two fields from Employee. The INLINE LOADs for Empoyee and Department then become:

Employee:

LOAD EmpId, EmpName, Title, Salary, CityId & '|' & DeptId AS DeptCityKey INLINE [

Department:

LOAD *, CityId & '|' & DeptId AS DeptCityKey INLINE [

Best,

Peter

Re: Circular reference

Anonymous — Tue, 05 Jan 2016 13:07:19 GMT

check this:

Circular References

What is Circular Reference?

Re: Circular reference

Tue, 05 Jan 2016 13:16:24 GMT

I want to be able to join the tables as I mentioned because I want to create a circular reference, which I know how to solve. This is a pretty normal ER modeling situation where 2 tables (Emp and Dept) link to a common lookup table (City) which results in a loop/circular reference.

The question is not about solving circular reference, rather help me join the tables as shown so that I create one.

Employee.DeptId = Department.DeptId AND

Employee.CityId = City.CityId AND

Department.CityId = City.CityId

Re: Creating Circular reference

Tue, 05 Jan 2016 15:32:08 GMT

Peter - Thanks for your response. I though about that, but the City in the Employee Table is the City in which the Employee lives where as the City in the Department table is the City where the department is located. Needless to day that they can be different.

In addition, the join between Employee and Department should only be on DeptId.

I am finding the automatic joining in QlikView quite frustrating. As a developer, if you can't join the tables as you'd like, then it is very limiting.

Re: Creating Circular reference

Tue, 05 Jan 2016 15:36:05 GMT

Arul,

Thanks for your reply. I tried Qualifying CityId in the both Employee and Department tables so that it gets excluded from the join, but it also removes the Join from Employee --> City and Department --> City.

I wish there is a way in QlikView to tell it how you as a developer want the joins. Employee and Department tables should only link on DeptId.

Re: Creating Circular reference

Anonymous — Tue, 05 Jan 2016 15:37:59 GMT

Have a read of this blog post by hic‌

Circular References

Re: Creating Circular reference

Tue, 05 Jan 2016 15:39:13 GMT

Bill - Thanks for your response. I have read this post before.

Re: Creating Circular reference

Peter_Cammaert — Tue, 05 Jan 2016 15:49:46 GMT

You want to keep those Employees that have their Department in the same city as the one they live in? That's what I understand when I read

Employee.DeptId = Department.DeptId AND

Employee.CityId = City.CityId AND

Department.CityId = City.CityId

as a single condition that should apply at all times

Re: Creating Circular reference

Tue, 05 Jan 2016 15:52:05 GMT

Thanks Peter!

My goal was to create a Circular Reference with this, and then solve it by Aliasing (loading it twice) the City table, because City table is a role playing table here.

This is for a school activity.

Re: Creating Circular reference

Anonymous — Tue, 05 Jan 2016 16:19:16 GMT

You could denormalise into 2 tables like this :

City:

LOAD * INLINE [

CityId, CityName

1, Columbus

2, Cleveland

3, Akron

4, Dublin

5, Hilliard

6, Powell

7, Grove City

];

MapDepartment:

Mapping

LOAD * INLINE [

DeptId, DeptName

1, Sales

2, IT

3, Marketing

];

Employee:

Load

ApplyMap ( 'MapDepartment' , DeptId ) as DeptName ,

;

LOAD * INLINE [

EmpId, EmpName, Title, Salary, CityId, DeptId

101, John Smith, Sales Mgr, 110000, 4, 1

201, Sara Ramirez, Sales Associate, 58000, 5, 1

213, Samuel Baker, IT Engineer 1, 63500, 1, 2

211, David Mcginti, IT Director, 145000, 2, 2

256, Alan Card, Business Mgr, 133000, 3, 3

];

drop field DeptId ;

With that now as your data model can you create the visualisations you need ?

Re: Creating Circular reference

Peter_Cammaert — Tue, 05 Jan 2016 16:33:08 GMT

You should not view them as JOINs per se. QlikView needs associations which may look like JOINs but they're used for a different purpose. QlikView will walk all those associations whenever you make a (different) selection in order to find out what data is related to your selection and what data is not. A real circular reference is forbidden in QlikView because this walking may never stop until the entire data model has been selected and then some. That's why at the end of a load script run, the engine will sort of split some tables in two (actually two copies) where associations will not propagate.

Your City table is an excellent example. You select an employee, that employee lives in a single city, but that city may have different departments and those departments provide work for more employees that all live in different cities, ... and so on.

Walking is more difficult than you would think