There are basically two different approaches I see here.
First you can use a SQL Transformation; the actual UPDATE statement can be constructed as a string, and so you can simply "translate" the week number of "today" (the day when the workflow runs) into the respective Week*_Tier attribute: week1 of the month to 'WEEK1_TIER', week 2 to 'WEEK2_TIER', and so on. Or as a simple expression:
'UPDATE table2 SET WEEK' || To_Char( indate, 'W') || '_TIER = ' || current_tier || 'WHERE...'
Then hand over this string to the SQL Transformation as e.g. a port with name QUERY; the "SQL Query" window of the SQL Transformation would simply read like this:
The second approach would use five different instances of TABLE2, all connected to the same Router transformation with five output groups (each output group corresponds to one of the five weeks of a month). This Router has the CUST_ID for the PK values of the five target instances and one WEEK_TIER input port.
All these instances are connected to the RTR via the CUST_ID, and each instance has one of the WEEK*_TIER attributes connected to the WEEK_TIER port of this RTR.
For example, the WEEK_TIER output port of output group 1 is connected to WEEK1_TIER of target instance 1.
The WEEK_TIER output port of output group 2 is connected to WEEK2_TIER of target instance 2.
And so on.