This content has been marked as final. Show 4 replies
Create an interface with CROI_EOM as a source.
Then create a filter like this in order to satisfy your point 2 :
V_E_DEP_ADV_BAL_GL_KEY is not null
OR V_E_CRE_DEP_BAL_GL_KEY is not null
OR V_E_DEP_RFU_ADV_BAL_GL_KEY is not null
OR V_E_DEP_CRE_ADV_BAL_GL_KEY is not null
OR V_E_DEP_BAL_GL_KEY is not null
In your target datastore, use "case when" syntax to satisfy your points 3,4,5,6
ex :"case when DEP_CRE_ADV_BAL <> 0 then Substr( DEP_CRE_ADV_BAL_GL_KEY , 14 ,11) ELSE Substr( DEP_DEP_ADV_BAL_GL_KEY , 14 ,11) END"
But for more details, give us more clear explanation about your need.
How many target columns do you have ?
What is your source and target technology ? (oracle database ? other database ? file ?)
Thanks for replying. The problem here I am facing is that -
If any 1 of CROI_EOM.V_E_DEP_ADV_BAL_GL_KEY , CROI_EOM.V_E_DEP_CRE_ADV_BAL_GL_KEY , CROI_EOM.V_E_CRE_DEP_BAL_GL_KEY , CROI_EOM.V_E_DEP_BAL_GL_KEY , CROI_EOM. V_E_DEP_RFU_ADV_BAL_GL_KEY is not null
then I have to select the 5 above mentioned columns but if there are more than 1 not null value then I have to
select DEP_ADV_BAL or DEP_CRE_ADV_BAL or CRE_DEP_BAL or DEP_BAL or DEP_RFU_ADV_BAL from the source table i.e. CROI_EOM.
That means,I need to have a count of not null values in the columns i.e. CROI_EOM.V_E_DEP_ADV_BAL_GL_KEY , CROI_EOM.V_E_DEP_CRE_ADV_BAL_GL_KEY , CROI_EOM.V_E_CRE_DEP_BAL_GL_KEY , CROI_EOM.V_E_DEP_BAL_GL_KEY , CROI_EOM. V_E_DEP_RFU_ADV_BAL_GL_KEY. I am struggling to get the count of this not null values.
So you have to count the number of keys that have a null amount value ?
What if 2 column of the same source row have a null value ? Do you have to count twice ?
By the way, I suggest you to create a target table whose each column stores the "number of not null values"
Example of columns :
In the mapping of this column, you do your count in function of DEP_ADV_BAL
Ex : COUNT ( distinct case when DEP_ADV_BAL is not null then V_E_DEP_CRE_ADV_BAL_GL_KEY else null END)
or SUM ( case when DEP_ADV_BAL is not null then 1 else 0 end)
You do that for each column you have to count.
If you want to historize the resultats, create another target column named of date format whose value is sysdate.