Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar

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

];

Capture.PNG

View solution in original post

8 Replies
sunny_talwar

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

];

Capture.PNG

Anil_Babu_Samineni

Sunny,

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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.

Capture.PNG

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

SET DateFormat='DD.MM.YYYY';

Not applicable
Author

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