Creating Circular reference

[#1]

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

[#2]

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.

[#3]

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.

[Anonymous]

Have a read of this blog post by hic‌

          Circular References

[#5]

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

[Peter_Cammaert]

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

[#7]

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.

[Anonymous]

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 ?

[Peter_Cammaert]

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