Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
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
Highlighted

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

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
Highlighted

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

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

Highlighted

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

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

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

Can you share the application where you tried it?

Highlighted

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

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

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

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

Highlighted

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

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

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

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

SET DateFormat='DD.MM.YYYY';

Highlighted
Not applicable

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

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