Conditional Lookup Please HELP

[oracledba]

I am getting health data for different states. I am giving my own logic in the transformer for the states CA and FL. Following this is a lookup

In the lookup,
I am looking up on two key values.

coverage code input file = coverage code from lookup data set
form no input file = form number lookup data set

picking up form number from the lookup to the output

If states are CA and FL, I dont want to lookup to be done but those rows should go through the lookup to the output data set so whatever logic I created in the transformer to output Form Number should be used.


The flow goes like this


TFM --> Lookup --> TFM--> Ouptut Dat Set




How can I implement this?

Tried 1

Lookup condition
LNK_COV.STATE <>'CA' OR LNK_COV_STATE <>'FL'

Condition Not Met
Fail
Lookup Failure
Reject


CA and FL states rows get rejected

---------------------------------------------------
Tried 2

Lookup condition
LNK_COV.STATE <>'CA' OR LNK_COV_STATE <>'FL'

Condition Not Met
Continue
Lookup Failure
Continue

All rows come out but the rows for the CA and FL are null and dont get loaded to output data set



17 rows 17 rows 12 rows
TFM --> Lookup --> TFM----------> Ouptut Dat Set


Tried 3

Lookup condition
LNK_COV.STATE <>'CA' OR LNK_COV_STATE <>'FL'

Condition Not Met
Drop
Lookup Failure
Fail

out of 17 rows only 12 rows get loaded. So the 5 rows of data for 'CA' and 'FL'' are getting dropped is what I am thinking.



17 rows 12 rows 12 rows
TFM --> Lookup --> TFM----------> Ouptut Dat Set


How can I design the flow so that the records for 'CA' and 'FL' states should go to the output data set through the same flow but the lookup shouldnt happen for those states specifically. All states are coming in and looked up and they all need to be outputted but 'CA' and 'FL' states shouldnt get looked up and go out to the same output data set. Is there a condition that can be put in the lookup and also how to set the Condition Not Met and Lookup Failure in this situation.

[ray.wurlod]

Why not use separate flows, one for CA and FL and the other for the other states? Only the latter performs the lookup, and the data flows are reunited using a Funnel stage.

[oracledba]

just came to that realization couple of hours after I posted the topic. Did exactly that by sending a flow with just CA and FL records and another flow with records <> CA and FL. Later used a funnel to reunite them together before sending to target dataset.

Thanks for getting back to me same day Ray