topic Re: Employee lists in QlikView

Employee lists

rassanbekov — Mon, 14 Aug 2017 08:19:25 GMT

Hello!

I have faced the problem. There are two lists of employees.

First contains the employee names for all departments.

The second contains only one department employees.

The data should be filtered by the one department employee only.

When I use "as" in script, the employees are properly matched. But the data for other employees are also loaded. How can I stop loading the data for all employee in Qlikview?

Re: Employee lists

jmvilaplanap — Mon, 14 Aug 2017 08:21:36 GMT

Hi,

Don't load the employees field of the second table, just comment that line.

Regards

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:23:49 GMT

Could you please specify?

I should somehow restrict loading...

Re: Employee lists

jmvilaplanap — Mon, 14 Aug 2017 08:26:01 GMT

For example:

Table1:

LOAD

Field1,

Employee,

Department

FROM table1

Table2:

LOAD

Field1,

Field2,

Field3,

Department

FROM table2

In the second load there is not the Employee field

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:29:29 GMT

In my case, employee name is a key (link between two files).

Re: Employee lists

ksomosera10 — Mon, 14 Aug 2017 08:32:23 GMT

Hi Ruslan,

Remove the field that causes the load of Employees, This will stop it from getting catch up during load times.

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:34:08 GMT

Could you please clarify?

Re: Employee lists

ksomosera10 — Mon, 14 Aug 2017 08:36:45 GMT

Can you show your script so that I could specify what you need to work out?

Re: Employee lists

jmvilaplanap — Mon, 14 Aug 2017 08:36:54 GMT

Can you share an example of you have and what do you want?

Thanks

Re: Employee lists

d_prashanthredd — Mon, 14 Aug 2017 08:41:52 GMT

Hi Ruslan,

You mean that you are having two tables, one with several departments and respective employees and other table with only one department employees.

Now, you want to load the data belongs to the one department only.. ryt??

Straight away you can go for Inner Join..

If, my understanding is wrong, please help me in understanding or provide some sample data.

Thanks,

Prashanth.

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:42:00 GMT

Thanks for support. Please see input data

Assume Table 1 consists of detailed information on all employees in Company (as attached file).

The Table 2 consist of only employees related to Department 1.

The task goal is to load into Qlik only the details relevant to Department 1 including details from Table 1. But no details on other emloyees needed.

Please help

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:42:51 GMT

Your understanding is correct. But i did not get on inner join....

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 08:43:15 GMT

Please see below attached input data

Re: Employee lists

jmvilaplanap — Mon, 14 Aug 2017 08:48:09 GMT

You can use a left join, for example:

LOAD

From Table 2.xlsx

left join

LOAD

[Department Name],

[Employee name] AS Employees,

[Other Details],

Salary

From Table 1.xlsx

Regards

Re: Employee lists

d_prashanthredd — Mon, 14 Aug 2017 08:52:32 GMT

Its working fine for me.. only thing is you need to map the fields to join.

All_Departments:

load * Inline [

Department name, Employee name, Other details, Salary

Department 1, A, 123, 100

Department 2, B, 454, 500

Department 3, C, 123, 100

Department 1, D, 321, 547

Department 2, E, 21, 726

Department 3, F, 11, 27

Department 1, G, 33, 378

Department 2, H, 6, 435

Department 3, I, 55, 431

Department 1, J, 11, 742

Department 2, K, 3, 475

Department 3, L, 22, 726

Department 1, M, 1, 9

Department 2, N, 2, 478

Department 3, O, 3, 640

];

Modified:

load [Department name] as Department, [Employee name] as Employees, [Other details], Salary Resident All_Departments;

drop table All_Departments;

Inner join

load * Inline [

Department, Employees

Department 1, A

Department 1, D

Department 1, G

Department 1, J

Department 1, M

];

Re: Employee lists

d_prashanthredd — Mon, 14 Aug 2017 08:56:10 GMT

Go through the sample app.

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 09:03:18 GMT

Thank you. But my Qlikview does not support you file extension.

Could you please rewite the script as it is make if simple loading data from excel is done. I can not understanding where in script I should write inner join my script looks like the following:

Directory;

LOAD

Department name as Department,

Employee name as Employees,

FROM [Data\Daily report.xlsx] (ooxml, embedded labels, table is Sheet1);

Directory;

LOAD

Department,

Employees

FROM [Data\june ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);

Left join or Inner join?

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 09:04:36 GMT

I cannot understand where in script to write left join?

I have the following script

Directory;

LOAD

Department name as Department,

Employee name as Employees,

FROM [Data\Daily report.xlsx] (ooxml, embedded labels, table is Sheet1);

Directory;

LOAD

Department,

Employees

FROM [Data\june ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$)

Re: Employee lists

rassanbekov — Mon, 14 Aug 2017 09:05:28 GMT

Please see below

Re: Employee lists

d_prashanthredd — Mon, 14 Aug 2017 09:10:12 GMT

Pls go through it..

Sorry, earlier I attached QlikSense app.

Thanks,