Create Rows Based on Missing Items from a Table

[steyngrobler]

Hi there,

I have three tables extracted from a learning management system's database. It entails records of checklists that we would like to prepare a progress report on. The problem is that the database only contains items for which learners have recorded progress. I would like to create a complete table that will show what items learners have yet to complete.

The three tables are:

1. Checklists (An overview of all the checklists)

id	course	name	introformat	timecreated	timemodified	useritemsallowed	teacheredit	theme	duedatesoncalendar	teachercomments	maxgrade	autopopulate	autoupdate	completionpercent	emailoncomplete	lockteachermarks
144	20	Logbook 3: Pumps	1	1,57E+09	1,58E+09	0	2	default	0	1	100	0	1	0	0	0
159	20	Logbook 3: Valves	1	1,57E+09	1,58E+09	0	2	default	0	1	100	0	1	0	0	0
160	20	Logbook 3: Drives	1	1,57E+09	1,58E+09	0	2	default	0	1	100	0	1	0	0	0

2. Checklist Items (An overview of all the checklists' associated checklist items)

id	checklist	userid	displaytext	position	indent	itemoptional	duetime	eventid	colour	moduleid	hidden	grouping	linkcourseid	linkurl
32	2	0	WM-01-WE01: Fabricate a variety of simple components using basic hand skills and tools	1	0	2	0	0	black	0	0	0	2
33	2	0	WA0101: Mark-off and fabricate a minimum of two flanges to given specifications	2	0	0	0	0	black	0	0	0	2

3. Checklist Checks (An overview of the items that users have ticked off)

id	item	userid	usertimestamp	teachermark	teachertimestamp	teacherid
21	33	5	0	0	0
22	33	2	1,57E+09	0	0
23	33	56	1,55E+09	1	1,54E+09	5
24	34	56	0	1	1,54E+09	5
25	35	56	0	1	1,54E+09	5

I have an overview of all user's user IDs. 

The problem is that the checks table only contains items that HAVE been checked off, not the ones that HAVE NOT. How would I go about generating additional rows for incomplete items? 

Please let me know if I can be more detailed. The tables are available for download here: https://we.tl/t-u5lpxFtj1r

I would really appreciate any assistance!

Regards,
Steyn Grobler

[Anonymous]

Couldn't you Take table1, which has the completed items, and do a merge, JoinKind.RightAnti, so that you then have all the rows in Table 1 that are NOT in Table3)?

[v-juanli-msft]

Hi @steyngrobler 

Based on my understanding, you have 912 ids in table "mdl_checklist_item", but only 381 items in table "mdl_checklist_check".

What you want is to have all 912 ids in mdl_checklist_check (corresponding to "item" in "mdl_checklist_item").

But i don't know what rows should be added.

I only can add additional ids under the original table.

for example, "mdl_checklist_check" has [id]: 1,2,3

id othercolumns

1  has value

2  has value

3  has value

"mdl_checklist_item" has [item] :1,2,3,4

item

1

2

3

4

then i can get a new table to replace  "mdl_checklist_check" like below:

id  othercolumns

1     has value

2     has value

3     has value

4     null

If it is ok, please check my pbix and ask me for details.

If not, please let me know what i need to explore more.

Best Regards

Maggie

[Anonymous]

Hi Maggie,

Thank you for your time and effort. It is really appreciated. I am sorry I am not clearer! I am sorry i did not respond sooner, but am having trouble with my forum account. I am now @Anonymous 

Yes, your understanding is correct. I want all itemIDs for all userIDs. This would be a much bigger table! Some of the item IDs won't apply to the users, but I can filter these out easily using the checklist ID.

This scenario would work for me:

---

1     has value

2     has value

3     has value

4     null

---

However, when I look at your table, there are no null values. Is my understanding correct? 

Thanks again for the effort!

Regards,

Steyn Grobler

[v-juanli-msft]

Hi @steyngrobler 

Sorry, i'm not clear about your bussiness rule.

Could you explain for your tables?

Which is the key column for every table?

How can we relate tables?

Best Regards

Maggie

[steyngrobler]

Hi Maggie, 

Thank you for your response. I appreciate the opportunity to express myself clearer - I was not sure where to start!

The tables connect in the below way. Please let me know if I misunderstood your query.

• Checklist[ID] = Checklist_Item[Checklist]
• Checklist_Item[ID] = Checklist_Check[Item]

In this way, the hierarchy is as follows:

1. Checklists - these are all your checklist activities in the LMS
2. Checklist Items - these are the individual items of which checklists are comprised
3. Checklist Checks - these are the individual checks that users have performed - it does not include what they HAVE not performed.

See screenshot below of how I've setup the relationships. The user table just provides names for each UserID.