topic Link filter without loop? in App Development

Link filter without loop?

oddgeir — Fri, 26 Jul 2024 12:25:48 GMT

Do you have any suggestions on how to match two dimensions without direct linking?

Details:
I have two tables with "Department", and would like department.Organization to be filtered according to department.StaffingPlans, so I can compare trends using the same filter.
Since I already have them both linked to the master calendar a direct link (i.e common dept.Link in both tables) will result in a loop warning and that one table is "loosely coupled". I'd prefer to be able to trust the results.
(I don't think going through a common department-period will solve the issue, as I am already using a "person-period" and one person can relate to different departments over time)

Is there any best practice way to solve such situations? I guess this has been a situation in many previous cases, but I was unable to find a good explanation on how to approach it.

Re: Link filter without loop?

SterreKapteijns — Mon, 29 Jul 2024 17:09:17 GMT

So I assume you mean that both tables are linked with a master calender, on for example a date field. When you also want to link the tables to each other with the department field, you need to create a composite key and a bridge/link table. The composite key will consist of both primary key fields: date & '-' & department. You create this field in both tables. Then you can create a link table to the master calendar. The link table will need to contain the composite key and the date field which will link to the master calendar.

So first: create composite key in both tables containing the current key to the calendar and the department field. Rename the date key field so the current link is being removed.

Then create the link table:

LINK:

load date

resident calendar

// then you will load all date fields, also the ones that do not occur in the other tables

inner join

load composite key field, date resident table1

inner join

load composite key field, date resident table2

in the end the link table will be linked to the calendar and the other 2 tables will both be linked to the link table, not to each other directly.

if any questions, let me know!

Solved: Re: Advantages of Link Table; - Qlik Community - 573849

link table - Qlik Community - 1883375

Re: Link filter without loop?

oddgeir — Tue, 30 Jul 2024 16:10:49 GMT

Thanks for what seems to be a good answer, if I didn't already have one composite link. I am trying to get a second common definition, which is not hierarchical, which makes it difficult to just add to the existing composite table.
("I don't think going through a common department-period will solve the issue, as I am already using a "person-period" and one person can relate to different departments over time")

I'll try to make my existing structure clearer.

Periods: Load * Inline [ Month 1 2 3 ]; Persons: Load * Inline [ From, To, Name , Department 1 , 2 , Anna , DeptA 2 , 4 , Anna , DeptB 1 , 4 , Bill , DeptA 1 , 3 , Charlie, DeptB 3 , 4 , Charlie, DeptC ]; PersonPeriods: [...doing the join as you mentioned...] => Anna1 , DeptA Anna2 , DeptB Anna3 , DeptB Bill1 , DeptA Bill2 , DeptA Bill3 , DeptA Charlie1 , DeptB Charlie2 , DeptB Charlie3 , DeptC

Now I know how the persons in the organization are planned to work. Which is the initial setup.
However I now want to set up the plans for a department, which is NOT related to the people, but where the end results should be seen together.

Departments: Load * Inline [ From, To, Department , Plans 1 , 2 , DeptA , 2 2 , 4 , DeptA , 4 1 , 4 , DeptB , 4 1 , 3 , DeptC , 1 3 , 4 , DeptC , 3 ];

Since people move between departments I can't go through them to handle the overall plans for the department. (For instance there would be no link to the plans for DeptC before Charlie starts in month 3.)
However when matching the plans with the actual organization I would like to be able to relate the filtering. If it was only for one department at the time I might use some variable, and set Analysis. But I'm not sure if that works when selecting multiple departments at the time. If this was SQL I guess I might be able to have a separate department table and use that selection as "where in (...)". But in my app I just don't see what's the best solution to achieve what doesn't seem to be to much to ask for.

So my desired outcome is to keep the current "who works where when" linked to the periods, and do the same with "staffing plans" (so far so good), and then be able to filter once to see both plans and actual staffing for the selected departments.
(I already have the setup and linking in place, but now it is required to select both from actuals and plans to match the selection)

This might seem to be much effort just to save one selection, but it is not the first time I have faced this situation, so I thought I'd check if there is some good solution I just haven't found yet.