Exception Report

[KmbrlyPC]

Hi! 

I have a list of student names and a list of weeks they are "present" in class. If I know all the student's names and I know there are 52 weeks in the year, how do I come up with a list of the exceptions? A list of the weeks that the students are absent in class? The weeks that are missing in the data are the weeks they are absent. Ultimately I want to combine the data so I have all student's names and all 52 weeks listed and a field that indicates "Present" or "Absent". Thanks for the help!

[terry10 · Accepted solution]

@KmbrlyPC 

See attached workflow. I used the Generate Rows tool for 52 WeekNumbers, appended to the list of student names, joined to your existing data, missing data was set as not Present. I wasn't sure what output you wanted, so I added am optional CrossTab.

Please mark this solution as accepted if it helped you. Thanks!

[KmbrlyPC]

Perfect, thank you!

[KmbrlyPC]

I have a follow-up question to this regarding the Generate Rows tool. I am needing to add a date as a new column. I need this date to be any day within the corresponding week. It can be the first day in the week or last, doesn't matter. It should still result in 52 rows, 1 for each week. How would I create this additional column using the generate rows tool?

[terry10 · Accepted solution]

@KmbrlyPC 

You can use the Generate Tools row to create a sequence of date, then derive week number from that. See attached.

I chose 2023-01-01 so you would see a full year, but you can change the start date in the Text Input tool.

[terry10]

BTW, if you post questions like this under Designer, rather than General, you will get responses sooner.

[KmbrlyPC]

Excellent, thank you again!