topic Two fields in the same table linked with another table. in QlikView

Two fields in the same table linked with another table.

Fri, 10 Jun 2011 09:37:42 GMT

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

Two fields in the same table linked with another table.

sparur — Fri, 10 Jun 2011 10:04:23 GMT

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

Two fields in the same table linked with another table.

Fri, 10 Jun 2011 11:16:50 GMT

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.

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

Fri, 10 Jun 2011 12:45:54 GMT

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

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

Fri, 10 Jun 2011 13:23:32 GMT

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;

Two fields in the same table linked with another table.

Fri, 10 Jun 2011 13:29:06 GMT

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

Two fields in the same table linked with another table.

Fri, 10 Jun 2011 13:44:25 GMT

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.

Two fields in the same table linked with another table.

Mon, 13 Jun 2011 08:29:43 GMT

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

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

Wed, 15 Jun 2011 12:16:16 GMT

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

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

Wed, 15 Jun 2011 13:34:13 GMT

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.

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

Wed, 15 Jun 2011 13:51:20 GMT

Thank you, I created a new thread there : http://community.qlik.com/thread/29919