Conditional Lookup Please HELP
Posted: Thu Sep 11, 2014 10:09 am
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.
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.