Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
alex-wb
Contributor III
Contributor III

Linking dates from two tables

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?  

alexwb_0-1652786508862.png

alexwb_1-1652786917174.png

 

 

Labels (5)
9 Replies
Or
MVP
MVP

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
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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

 

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution.
If you want to go quickly, go alone. If you want to go far, go together.
Or
MVP
MVP

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

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.