Load just specific data

[jakobjosef]

Hi dear Community, 

I have a question and I hope there is a simple solution again.

For my dashboard, I want to load 2 dimensions/tables. 

First dimension/table: staff data (Name, Employee-ID, e-mail, department, ManagersName)
Second dimension/table: staff costs (Employee-ID, date, salary, payroll taxes etc.)

Since I do create this dashboard just for one specific manager (e.g. Adam Johnson), I am loading the first dimension just for his employees:

load * where match(ManagersName, 'Adam Johnson'). 
Until here everything is fine. 

Now I try to load the second dimension/table - but the problem is that I get staff costs for the whole company, but I just want staff costs to be loaded for employees of  Adam Johnson. 

So I am looking for a script statement which says:
Load * from dimension staff costs where employee-ID is a ID where Manager Name = Adam Johnson.

Maybe I have to use LET-statement? I know  how to restrict data-load for the  "current" table, but I dont know how to make a restriction which needs to be connected to another dimension/table.

Hope there is a easy solution for my problem 🙂

Have a nice day,
Jakob

Edit:  the 2nd table doesn't contain the field "ManagersName"

[albert_guito · Accepted solution]

Hi,

You can use a where clausule in the second table to load only the existing id in the first table

Load ... Where EXISTS(Employee-ID);

Ag+

[ahmed_hassan]

HI,

I am not sure if I get your problem right, but I assume you want to load "two" tables which contain data of employee (who are working under  Adam Johnson).

As I can see, the 2nd table doesn't contain the field "ManagersName". Therefore, I would advise you to use a "left join" based on "Employee-ID"

Table2:
load * 
From table 2;

left join (Table2)
Load
Employee-ID,
ManagersName
from table 1;
 

then you can use "load * where match(ManagersName, 'Adam Johnson'). " for table 2 as well. 

in case you face any problem, try to use "Where WildMatch(ManagersName,'*Adam Johnson*'); "

Good luck!

[jakobjosef]

Hi Albert, 

thank you for your quick response!

I already used this solution, but since there is a fluctuation concerning the emyployees (some get recruited, some leave the department/company) I had to update this statement everytime there was a change regarding staff.

I hope there is a automatically solution, where I do not need to check employee-ID every week.

But thank you for your proposal!

Have a nice day
Jakob

[tresesco · Accepted solution]

Try using exists(), like:

Load * From <staff table> where match(ManagersName, 'Adam Johnson') ;

Load * From <staff cost table> where exists(Employee-ID);

[jakobjosef]

Hi Tresesco, 

thank you for your answer - it's the same problem with alberts proposal: The Employee-ID is not static but is changing every week since employees are leaving/new ones get recruitet. 

Or does it get updated automatically?
I used a statement like 

Load * From <staff cost table> where match(Employee-ID, 10060, 10070, 10080 etc. );

[jakobjosef]

Hi Ahmed, 

thanks so far, I'll try now and let you know - never used wildMatch 🙂

Jakob

[tresesco]

Hi Jakob,
I am not sure if I understood you right. The expression, ...Exists(Employee-ID).. is not static. With this statement, you tell qlik to load those emp ids that are already loaded in the previous load. That means, if one emp goes or comes in the staff table that is being taken care of dynamically. Try it and let know.

[jakobjosef]

ah ok I didn't know that!

I'll try 🙂

[albert_guito]

Hi Jakobjosef,

You don't need to declare a Match clausule.

When you load the first table with the "where Manager Name = Adam Johnson" only the Employee-ID related to this Manager will be "available" for the Exists clausule. It's automatic.

The code is @tresesco has proposed.

Ag+