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

[#1]

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

[sunny_talwar · Accepted solution]

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

];

[Anil_Babu_Samineni]

Sunny,

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

[sunny_talwar]

Can you share the application where you tried it?

[Anil_Babu_Samineni]

Thanks,

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

PFA

[sunny_talwar]

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.

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

SET DateFormat='DD.MM.YYYY';

[#9]

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