Forum Discussion
Help Creating merged table with all potential combinations in rows
Can you share sample data of both tables as well as expected result
Do not share any sensitive data
Absolutely, Sample data below:
Sample table 1: submissions by date / employee (plus some additional columns of information not relevant to this task):
note: dates are always the last 6 mondays.
note: there are over 150 employees in the real dataset.
| Employee | Date | other data… |
| Employee1 | 21/11/2022 | data here |
| Employee3 | 21/11/2022 | data here |
| Employee4 | 21/22/2022 | data here |
| Employee1 | 14/11/2022 | data here |
| Employee1 | 07/11/2022 | data here |
| Employee2 | 07/11/2022 | data here |
| Employee3 | 07/11/2022 | data here |
| Employee4 | 07/11/2022 | data here |
Sample Table 2: A list of all employees submitting data (plus some supplementary columns not relevant to this task):
note: each employee only has one record in this table, there are no duplicates
| Employee | other data… |
| Employee1 | data here |
| Employee2 | data here |
| Employee3 | data here |
| Employee4 | data here |
| Employee5 | data here |
Expected output table: TRUE / FALSE for submissions by employee / week
| Employee | Week | Submission |
| Employee1 | 21/11/2022 | TRUE |
| Employee2 | 21/11/2022 | FALSE |
| Employee3 | 21/11/2022 | TRUE |
| Employee4 | 21/11/2022 | TRUE |
| Employee5 | 21/11/2022 | FALSE |
| Employee1 | 14/11/2022 | TRUE |
| Employee2 | 14/11/2022 | FALSE |
| Employee3 | 14/11/2022 | FALSE |
| Employee4 | 14/11/2022 | FALSE |
| Employee5 | 14/11/2022 | FALSE |
| Employee1 | 07/11/2022 | TRUE |
| Employee2 | 07/11/2022 | TRUE |
| Employee3 | 07/11/2022 | TRUE |
| Employee4 | 07/11/2022 | TRUE |
| Employee5 | 07/11/2022 | FALSE |
My only other note is that an employee can and usually will submit more than one record of information per date. But here we're only interested in flagging if an employee has submitted anything at all. The final output for reporting would filter out any TRUE values, leaving us with this:
| Employee | Week | Submission |
| Employee2 | 21/11/2022 | FALSE |
| Employee5 | 21/11/2022 | FALSE |
| Employee2 | 14/11/2022 | FALSE |
| Employee3 | 14/11/2022 | FALSE |
| Employee4 | 14/11/2022 | FALSE |
| Employee5 | 14/11/2022 | FALSE |
| Employee5 | 07/11/2022 | FALSE |