Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I was wondering how I could link two different fields of the same table to another table.
In my case, I have two tables: The table User, and the table UserHierarchy.
The table UserHierarchy contains the following fields: IdHierarchy, IdUser, IdUserChild. Both IdUser, IdUserChild refer to rows in the table "User".
Now, I would like to display in a chart the list of employees of a user, but I can't come up with a way to achieve this.
Could someone provide any help ?
Thank you very much
Julian
Easiest is to denormalize. Just create different dimensions for employee and manager.
Hello, Julian.
if you no need a link by both fields (IdUser and IdUserChild) you can rename one field (I think it should be IdUserChild) in User table. after this you will get a link by one field - IdUser
Julian,
In this case you should make a new field wich would act as a composite key.
It´s very easy, just have to concatenate both fields and name it "User_Hierarchy_key" for example:
User:
LOAD
IdUser,
IdUserChild,
IdUser & '^' & IdUserChild as User_Hierarchy_key,
....
Create this field in both tables. Then, remove from one of the two tables the fields involving the composite key. For example, remove IdUser and IdUserChild from the User_Hierarchy table and keep them in the User table.
If you have many records in some of the two tables involved, use de AUTONUMBER() function to build a more compact key (translates the concatenated string to a number, which is more memmory and calculations efficient).
AUTONUMBER( IdUser & '^' & IdUserChild ) as User_Hierarchy_key,
Hope this helps.
Thank you for your answers. I still have a problem I don't manage to get ride of: I declare my table as follows:
[User]:
Load
Id as IdUser,
Id as IdUserChild,
IdUser & '^' & IdUserChild as User_Hierarchy_key,
LastName,
FirstName,
Level
// WHERE Id = $(userid)
;
SQL SELECT
*
FROM "*****".dbo."User";
But it won't download my database this way, I get the following message:
#####################
Field not found - <IdUser>
SQL SELECT
*
FROM "******".dbo."User"
#####################
If I remove the line "IdUser & '^' & IdUserChild as User_Hierarchy_key,", my script works fine but QV creates a Syn table (is it the normal behaviour ? I've read that it's better to get ride of them)
Thank you once again,
Julian
I’m not sure to understand your problem, but I hope this will be helpful :
For the “field not found”, if you rename a field in a load statement you cannot use it in the same load statement, you have to do something like :
load
Id as IdUser,
Id as IdUserChild,
IdUser & '^' & IdUserChild as User_Hierarchy_key;
load
Id as IdUser,
Id as IdUserChild;
sql select...
For theSync table it’s the normal behavior, Qlikview link fields on their names and can do a link only on one field, so if two tables have more than one common field you will have a sync table.
If you have only two levels of users the simplest way is to create a table for managers and another one for employee :
tmpUser:
load
Id as IdUser,
Name
sql select...
Hierarchy:
load
IdHierarchy,
IdUser,
IdUserChild
sql select...
User:
load distinct
IdUser
resident Hierarchy;
left join load
IdUser;
Name
resident tmpUser;
UserChild:
load
IdUserChild
resident Hierarchy;
left join load
IdUser as IdUserChild;
Name
resident tmpUser;
drop table tmpUser;
Thank you for your answer. I will try the code you provided.
Unfortunately, I have 3 levels of users, and we will probably create other levels later
That's because IdUser and IdUserChild don't exist in your source table. From what I can gather, Id is the name of the field which you have renamed in this table. Try using Id in your expression instead.
Easiest is to denormalize. Just create different dimensions for employee and manager.
Thanks everybody, I denormalized this part of my db and created a different dimension, it did the trick!
I still have a problem, maybe someone 'll be able to help me. In a chart, I have the following expression (it's a dimension) : =if(IdUser=userid, IdUserChild)
The other columns display data about the User (because I used the IdUser field, part of the User table), and not about his child (which is also a User, but I would like QV to use the IdUserChild (FK attached to the User table) field as the identifier to be used, and not IdUser).
Better create a new thread for that question, cos now in the list it's marked as coorectly answered. And you might want to elaborate a bit more. Try giving a short example with INLINE data and what you expect to be the result.