Wouldn't a preceding load do the trick?
if( [short_policy]='Global' and [n carrier] = '000253', 'Global2', null()) as [Filter_Global];
if([n carrier] = '000253', if(left([policy], 3)='920', 'Global', if(left([policy], 3)='609', 'Global',
'General'))) as [short_policy]
drop table [HYP2];
thanky for your reply. I tried implementing a preceding load, but what happens is that the first load limits the second load to only those values for the conditions in the firs load are fullfilled.
In other words, the two loads are not loading all the 30k rows adding the 'Global' tag to some rows, but loading only those rows that have the 'Global' tag.
Any other suggestion?
Say now, with these two loads I have the 100+ rows marked. How can I add all the rows the original rows excluding repetitions?
You should avoid Left Joins in this case, because it will potentially change the number of records and you do not want this. (It will if you have multiple records with the same combination of [n cli] and [n carrier].)
My suggestion does not contain a where-clause in the preceding Load, so it will not change the number of records. I.e. if you have 30k records in the "LOAD ... resident [HYP2]", then you will have the same number after the preceding Load also.
And you want to define the [Filter_Global] from these 30k records, right? Then a preceding Load is the correct way to do it. Or am I missing something?