09-20-2016 05:11 AM
The lookup transformation works lovely if you want to check if values are present in the Lookup table. But what if I want to check if a key is not present in the lookup table and keep these rows?
In my example I have two lookup tables. I want my records of the source table to be present in one lookup table but absent absent in the other lookup table.
The code to my transformation looks like this:
set isdata.ISDV_AKT end = eof; /* Is the current key value stored in hash h0? */ rc0 = h0.check(key: bnr, key: COMP); /* Is the current key value stored in hash h1? */ rc1 = h1.check(key: bnr, key: COMP); /* Examine success of lookups */ if ( rc0=0 and rc1=0 ) then do; /* Write row to target */ output work.festgeworden; end;
But I want the "Examine success of lookups" line to look like this:
if ( rc0 = 0 and rc1 ^= 0 ) then
Is there an option to keep the rows if the check is negative (rc1 ^= 0) and skip those who were checked successfully?
Or do I have to use user written code in this case?
09-20-2016 05:25 AM
I don't have DI Studio at my fingertips, but from the top of my head I don't think that Lookup can give you this.
Perhaps you could use the Data Validation transform, or use Join with a NOT EXISTS/NOT IN() construct.
What is your goal to sort keep those records? Perhaps there's another way, depending on your objectives.
09-20-2016 05:56 AM
09-20-2016 07:29 AM
An idea, do the Lookup, and use Extract in the next step to filter out missing keys.
Again, how will you use entries without keys?