8 Replies Latest reply: Jul 4, 2016 3:14 AM by Viktor Ryzhov RSS

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

    Viktor Ryzhov

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