Linking dates from two tables

[alex-wb]

Hi,

I have two tables. One shows the shifts that someone was meant to work, and the other shows what they did on the shift. 

The EIN is a completely unique field. In the schedule table each day is unique however in the handing table dates will be repeated per action taken. 

The issue I am having is that I have a pivot table that sums all shifts worked by the analyst, and everything they did. When I filter on the date, nothing changes. I tried creating the date cal table in the same format as a concal but it still doesn't work. I can't merge the tables and am at a loss for what to do.

Any suggestions?  

[Or]

What does the EIN field actually contain? Do the contents correspond with the date / shift information? This would need to be the case here, since there are no other fields in the join...

[alex-wb]

Hi Or,

Thanks for the reply.

The EIN is a unique analyst number that appear is both tables. It is a  numerical field. It  may be repeated however due to an analyst being scheduled to work multiple days which would create a row per day, and also an analyst can do multiple tasks on multiple days which would create multiple rows per task . 

[Or]

In that case, your join is incomplete - there is nothing telling Qlik which row in each table corresponds to which row in the other tables. It only knows how to match the analyst, but not the date or shift. You would need to add those fields / that information to your join (by creating a key field or by making sure the fields exist in both tables with identical names).

[alex-wb]

Thanks Or, but isn't that what I am doing in my DateCal table? 

[vikasmahajan]

Hi,

Have you check this link table concept ?

https://community.qlik.com/t5/Qlik-Sense-Documents/link-table/ta-p/1883375

Vikas

[Or]

Perhaps the issue is on my end, but I don't see any actual joins between your DateCal table and your two other tables except the analyst ID. Neither of them appears to connect to any key or date values that would reduce the data based on the date selections?

[alex-wb]

That is the main join, but all dates have been merged in the datecal and I have a flag to Say which date to use. Are you saying it would be better to link using a date field?

[alex-wb]

Hi , thanks for the suggestion. I am not sure how this would work, the main issue I have is being able to filter both tables using 1 date.

[Or]

What I'm saying is that it doesn't appear there are any actual joins on the dates - if I read this correctly, the dates will reduce the middle table, and then each of the other two tables will be reduced to the relevant analysts for that date period, but nothing will cause the two outer tables to be reduced by dates as the only key field is the analyst ID.

You could "fix" this by adding a join based on the date and shift (assuming they exist in both outer tables), or as most people prefer - create a composite key from the fields in question and join based on that.