How to Join 3 tables in a way I want...

[#1]

Dear All,

I have the following problem. I load 3 tables to QV:

Cost Center:

Cost Center,

Responsible,

Reference,

company name

Manager:

User Name,

Reference,

Manager

Mgr Reference

Limit:

User Name,

Reference,

Limit

with the following limitations/conditions:

- I can not change names of loaded fields in data source

- Responsible from Cost Center table contains the same information as User Name in other tables

- User Names in Limits table contain the authorisation limits both for User Name and Manager from Manager Table

- as one user name can have more than one limit, the connection of  User Name & Reference is distinctive dimension to apply limit. This logic is kept in all tables

I want to load to QV a data in a away to get one table containing for each CC existing on CC table:

Cost Centre

Responsible

Reference

Manager

Limit for Responsible

Limit for Manager

I managed to load the data, but without last domension in the following way:

Load Cost Centre

***

Left keep (Cost Centre)

Load manager

***

Left keep (Cost Centre)

Load Limit

***

I am fighting to have a limit for manager as well... I was trying first to add a new dimension to Manager table (called Limit_Mgr) by ApplyMap formula, however the problem is that dimensions containing the same data (Manager in Manager table and User Name in Limit table) are called differently, and this formula does not work. The lookup formula does not work as well... How can I do that then..?

Tha nks in advance for your help

[kuba_michalik]

KEEPing will not result in a single table.

You can join tables on a field with different name by aliasing the field name in the table you are LOADing during the join. So, more or less something like this, assuming all tables are loaded into QlikView to start with:

Left Join ([Cost Center])

LOAD

[User Name] as Responsible, /*this is the key part*/

Reference,

Manager

[Mgr Reference]

Resident Manager;

Left Join ([Cost Center])

LOAD

[User Name] as Responsible,

Reference,

Limit as [Limit for Responsible]

Resident Limit;

Left Join ([Cost Center])

LOAD

[User Name] as Manager,

Reference as [Mgr Reference],

Limit as [Limit for Manager]

Resident Limit;

Then, presumably, drop the Manager and Limit tables if you don't need them for anything else.

[#3]

Hi

ONE BIG MISTAKE FROM MY SIDE...

TABLE COST CENTER DOES NOT CONTAIN MANAGER DIMENSION

I am sorry, i just noticed that I made this mistake in my original post.

So the set up is:

CC table - contains CC responsible

manager table - assigns managers to users (All CC responsible are among the users)

Limit table: contains Limits for all the users in the company (CC responsible + their managers + other as well)

[kuba_michalik · Accepted solution]

That shouldn't change anything in my example. First left join will add manager to the Cost Center table, and so it can be used in subsequent joins.

[#5]

Dzieki Jakub, Zadziałało...

It worked... I did not know that it is possible to join the dimensions (manager in my case) that are not in the main table (Cost center in my case)

now i know 🙂

Thanks a lot