Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Two fields in the same table linked with another table.

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

1 Solution

Accepted Solutions
Not applicable
Author

Easiest is to denormalize. Just create different dimensions for employee and manager.

View solution in original post

10 Replies
sparur
Specialist II
Specialist II

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Easiest is to denormalize. Just create different dimensions for employee and manager.

Not applicable
Author

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).

Not applicable
Author

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.