Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Two fields in the same table linked with another table.

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

10 Replies
sparur
Valued Contributor II

Two fields in the same table linked with another table.

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

Two fields in the same table linked with another table.

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

Re: Two fields in the same table linked with another table.

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

Re: Two fields in the same table linked with another table.

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

Two fields in the same table linked with another table.

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

Two fields in the same table linked with another table.

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

Two fields in the same table linked with another table.

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

Not applicable

Re: Two fields in the same table linked with another table.

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

Re: Two fields in the same table linked with another table.

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.

Community Browser