How manage an auto-refers inside QV

[#1]

Hi.

I need to know how QV manages an auto-refers.

F.e. I could read a Personnel or Employees table with EmployeeCode as PK and ManagerCode as FK linked to the same table. I know that in QV a relation is stablished by maintaning the same name between fields to link. But in this case, I have a DUMP for the load script and I must use two different names, EmployeeCode and ManagerCode.

How can I solve this issue, please? Thanks

I'm using QV 10 SR1.

[brenner_martina]

Hi,

thats easy, load the fields again and rename them, something like:

Load EmployeeCode As %EmployeeKey,
EmployeeCode,
....
From Table1;

Load ManagerCode As %EmployeeKey,
ManagerCode,
....
From Table2;

[#3]

I think you have to split information on twpo distinct tables, one for employees, and the other just for managers, try something like that :

employee:
load EmployeeCode, ManagerCode, otherInfo... from where you want

manager:
load Distinct ManagerCode resident employee;
left join load EmployeeCode as ManagerCode, otherInfo as managerOtherInfo resident employee;

[#4]

Sorry, but I don't understand!

Why Table1 and Table2? I have only the Personnel (or Employees) table with (f.e.) the following field:

1. EmployeeCode

2. FirstName

3. LastName

4. ManagerCode

A Manager also belongs to the Personnel (or Employees) table. Into the underlying db, it exist an auto-reference for the Personnel table.

[#5]

Hi, I'm a beginner with QV. Do you think or are you sure?

This case occurs frequently. Is there a best practice to manage it?

Moreover, for now, I'm using the statement SQL and the statement STORE to create the QVD files and then, in a QVW document, I will use the LOAD statement.

Many thanks

[johnw]

pscorca69 wrote: Hi, I'm a beginner with QV. Do you think or are you sure?
This case occurs frequently. Is there a best practice to manage it?

Separating managers and employees into two different tables is one option. However, out in the real world with real organization hierarchies, I'm guessing that won't tie your data together the way you want.

You could probably use a hierarchy load on the data. I haven't used hierarchy loads outside of a few test cases, but it could probably be used to store an organization hierarchy.

You could use a semantic load on the data. Everyone would be stored as employees, and you'd also store the relationship between employees, such as the manager->managed relationship. You can then navigate through the "hierarchy" by following these semantic links up and down.

I'm sure there are other options as well. Best practice depends on your exact requirements.

[#7]

Hi John,

I want to simply translate the Employees-Managers relation in QV. In SQL Server, generally it is enough to create a Employees entity or table with Employee Code as PK and Manager Code as the FK to link the Employee code, generating an auto-refers relationship. In QV, the relations are created by the field naming.

Thanks

[brenner_martina]

Hi,

believe me, QlikView is just working like you want, you have to load the same table again, 1. as PK, second as FK without any other fields:

Load EmployeeCode As %EmployeeKey,
EmployeeCode,
ManagerCode as OrgManagerCode,
....
From Table1;

Load ManagerCode As %EmployeeKey,
ManagerCode
From Table1;

[#9]

Ok Martina, in your first reply you have written "From Table2" in the 2nd LOAD statement but I have understood it is a mistake.

Don't worry. But I think it isn't necessary to write two different Loads: so it is writting two logical tables for one physical table.

Thanks

[farolito20]

How you solved this?