topic Re: Current Department Problem - How to put in column the filtered value from another table in QlikView

Current Department Problem - How to put in column the filtered value from another table

Sat, 02 Jul 2016 12:00:42 GMT

I have example tables as following:

Departments: // It describes when Employee changes his workplace and stars to work in different department

LOAD * INLINE [

DateOfChangingPosition, Name, Department

15.12.2015, Ivanov, Sellers

15.12.2015, Petrov, Accountants

15.12.2015, Sidorov, Drivers

15.01.2016, Ivanov, Accountants

15.03.2016, Ivanov, TopManagement

15.02.2016, Petrov, Drivers

];

Salary: // Registry of salary per employee

LOAD * INLINE [

DateOfSalary, Name, SalaryAmount

01.01.2016, Ivanov, 100

01.01.2016, Petrov, 150

01.01.2016, Sidorov, 180

01.02.2016, Ivanov, 100

01.02.2016, Petrov, 150

01.02.2016, Sidorov, 180

01.03.2016, Ivanov, 100

01.03.2016, Petrov, 150

01.03.2016, Sidorov, 180

01.04.2016, Ivanov, 100

01.04.2016, Petrov, 150

01.04.2016, Sidorov, 180

];

And I need to get from this data such result table - Salary per Departments:

To get that result I need to add one more column in Salary table with calculation of current department of Employee (actual on that day). I need to filter Department table using 2 conditions: same Name and DateOfSalary >= DateOfChangingPosition. Afterwards take maximum (more recent) DateOfChangingPosition and use actual department from that row.

And I don't understand how to do this in Qlik. I will appreciate any help ))

Re: Current Department Problem - How to put in column the filtered value from another table

sunny_talwar — Sat, 02 Jul 2016 14:13:06 GMT

May be like this:

Departments: // It describes when Employee changes his workplace and stars to work in different department

LOAD * INLINE [

DateOfChangingPosition, Name, Department

15.12.2015, Ivanov, Sellers

15.12.2015, Petrov, Accountants

15.12.2015, Sidorov, Drivers

15.01.2016, Ivanov, Accountants

15.03.2016, Ivanov, TopManagement

15.02.2016, Petrov, Drivers

];

Dept:

LOAD Name,

Department,

Date(DateOfChangingPosition + IterNo() - 1) as Date

While DateOfChangingPosition + IterNo() - 1 <= EndDate;

LOAD *,

Date(If(Name = Previous(Name), Previous(DateOfChangingPosition), Today())) as EndDate

Resident Departments

Order By Name, DateOfChangingPosition DESC;

DROP Table Departments;

Right Join (Dept)

LOAD *,

DateOfSalary as Date

INLINE [

DateOfSalary, Name, SalaryAmount

01.01.2016, Ivanov, 100

01.01.2016, Petrov, 150

01.01.2016, Sidorov, 180

01.02.2016, Ivanov, 100

01.02.2016, Petrov, 150

01.02.2016, Sidorov, 180

01.03.2016, Ivanov, 100

01.03.2016, Petrov, 150

01.03.2016, Sidorov, 180

01.04.2016, Ivanov, 100

01.04.2016, Petrov, 150

01.04.2016, Sidorov, 180

];

Re: Current Department Problem - How to put in column the filtered value from another table

Anil_Babu_Samineni — Sat, 02 Jul 2016 15:01:32 GMT

Sunny,

I tried same thing, But here it showing like below.

Re: Current Department Problem - How to put in column the filtered value from another table

sunny_talwar — Sat, 02 Jul 2016 15:03:13 GMT

Can you share the application where you tried it?

Re: Current Department Problem - How to put in column the filtered value from another table

Anil_Babu_Samineni — Sat, 02 Jul 2016 15:05:26 GMT

Thanks,

Sunny, Please let me know if any changes from this Pivot table, Please?

PFA

Re: Current Department Problem - How to put in column the filtered value from another table

sunny_talwar — Sat, 02 Jul 2016 15:09:59 GMT

I have no idea what you are trying to do. You combined your application with another person's application?? Department field is all null. No idea what you trying to do my friend.

Re: Current Department Problem - How to put in column the filtered value from another table

Anil_Babu_Samineni — Sat, 02 Jul 2016 15:15:40 GMT

Sunny,

I just copy and paste of your Code and then i choose Pivot and then Dim - Department, Dateofsalary

Expr - Sum(SalaryAmount)

Do i require to change any other check boxes. I just remind you, Department data showing 0 Values.

- Anil

Re: Current Department Problem - How to put in column the filtered value from another table

sunny_talwar — Sat, 02 Jul 2016 15:19:15 GMT

I get it now. You have not changed the environmental variable:

SET DateFormat='DD.MM.YYYY';

Re: Current Department Problem - How to put in column the filtered value from another table

Mon, 04 Jul 2016 07:14:22 GMT

You are awesome! It works perfectly! I just amended one thing:

While DateOfChangingPosition + IterNo() - 1 < EndDate; //Because otherwise it generates 2 rows for the same date (date of changing position)

Can you please provide some more information about usage of that structure:

While DateOfChangingPosition + IterNo() - 1 <= EndDate;

LOAD *, //Especially this line - for what reason it is here?

Date(If(Name = Previous(Name), Previous(DateOfChangingPosition), Today())) as EndDate

Maybe some links - it would be very useful for me ))