after your 4 joins, the overall resulting table will be Table 1. You'll need to drop tables 2, 3 and 4.
As far as your if() statement, you can do one of the two:
1. Reload the final resulting table after all the joins are made - this is the most readable solutions, and I'd prefer it:
IF(table2.field1 = 'ACR', 'Hello', table1.Category) as MainCategory
drop table Table1;
Another way - if you REALLY want to use preceding load, you should position it right before the last (4th) join.