Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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

8 Replies

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

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

Life is so rich, and we need to respect to the life !!!

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

Can you share the application where you tried it?

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

Life is so rich, and we need to respect to the life !!!

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

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

Life is so rich, and we need to respect to the life !!!

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';

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

Community Browser