topic Re: Load just specific data in App Development

Load just specific data

jakobjosef — Tue, 11 Dec 2018 09:01:32 GMT

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"

Re: Load just specific data

albert_guito — Tue, 11 Dec 2018 08:44:06 GMT

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+

Re: Load just specific data

ahmed_hassan — Tue, 11 Dec 2018 08:47:48 GMT

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!

Re: Load just specific data

jakobjosef — Tue, 11 Dec 2018 08:50:34 GMT

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

Re: Load just specific data

tresB — Tue, 11 Dec 2018 08:50:50 GMT

Try using exists(), like:

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

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

Re: Load just specific data

jakobjosef — Tue, 11 Dec 2018 08:56:54 GMT

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

Re: Load just specific data

jakobjosef — Tue, 11 Dec 2018 08:59:01 GMT

Hi Ahmed,

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

Jakob

Re: Load just specific data

tresB — Tue, 11 Dec 2018 09:06:15 GMT

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.

Re: Load just specific data

jakobjosef — Tue, 11 Dec 2018 09:06:25 GMT

ah ok I didn't know that!

I'll try 🙂

Re: Load just specific data

albert_guito — Tue, 11 Dec 2018 09:10:12 GMT

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 @tresB has proposed.

Ag+

Re: Load just specific data

jakobjosef — Tue, 11 Dec 2018 09:48:44 GMT

HA!

WORKED!

now I know what the Exists-Clause should be used for!

Thank you so much everybody 🙂

Jakob