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

[abirami_palanis]

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

[arulsettu]

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

];

[tyagishaila]

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.

[tyagishaila]

to remove circular reference, you can concatenate these tables.

[Anonymous]

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.

[PrashantSangle]

Hi,

use link table concept.

Regards,

[Peter_Cammaert]

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

[Anonymous]

check this:

Circular References

What is Circular Reference?

[#10]

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