I have found a solution to my problem. I was using text field to join. It seems like there's a character limit when using text field as index for intersection. What I did was to substring the index text field into only shorter unique text. It's not ideal but it works.
e.g.
substring(Rtrim(Ltrim(Upper(@textfield))),1,12)