DSXchange

Hi,
I am having a job in which I need to create a new column and update this with "y" or "N" based on the result of previous join. Can anybody tell how to do this? what stages to be used etc.

Thanks
Kavuri

You can create a new column in transformer. Check for the logic in the transformer and populate the value "Y" or "N".

Yes, I created a column by using column generator stage. And I placed a Trasformer stage after column generator.
Here I need to compare fields from two tables one from PROFOTHER_FACT with column name as PROFOTHER_SERVICE_ID and STAY_LINK_FACT with column name as RELATED_SERVICE_ID , If both are equal

and

another field LINK_TYPE_CODE is either "ERPFO" or "HSPFO".

Then, I need to update the value with "Y" or "N".

i tried using If(bhi.PROFOTHER_FACT.PROFOTHER-SERVICE_ID = bhi.STAY_LINK_FACT.RELATED_SERVICE_ID AND LINK_TYPE_CODE = "ERPFO" OR LINK_TYPE_CODE = "HSPFO")

I know that I need to modify the logic in the above, but first of all does the transformer stage will take a table script in it or not?. It is showing in red , if I edit the column.

Anu suggestions appreciated.

Thanks
Kavuri

Use a join stage to join the table first. Then in the transformer check for the condition "LINK_TYPE_CODE is either "ERPFO" or "HSPFO"" and populate the new field. And for the join stage you need to have same field names. So use a copy stage and rename the fields and switch off RCP in the copy stage.

Hi Maveric,

If I understand correctly, i think the one you suggested will not work for me.
because, I need to get all the rows from PROFOTHER_FACT table and I need to update inp_ind field with "Y" if PROFOTHER_FACT.PROFOTHER_SERVICE_ID = STAY_LINK_FACT.RELATED_SERVICE_ID. and the rest.

Thanks
Kavuri

What part wont work? You need the data from both the tables. So join. And then validate the condition in the transformer. You can also drop the fields that you don't want in the transformer.

kavuri wrote:Hi Maveric,

If I understand correctly, i think the one you suggested will not work for me.
because, I need to get all the rows from PROFOTHER_FACT table and I need to update inp_ind field with "Y" if PROFOTHER_FACT.PROFOTHER_SERVICE_ID = STAY_LINK_FACT.RELATED_SERVICE_ID. and the rest.

Thanks
Kavuri

I can't think why it will not work for you;
Can't you use "Left Outer Join" and then check following constraints in transformer:

Hi,
As you said I made a join and the join type as leftouterjoin. So I am getting all the rows from input, and in the transformer stage I had written the following code.

If (DSLink45.LINK_TYPE_CODE = "ERPFO" Or DSLink45.LINK_TYPE_CODE = "HSPFO") Then DSLink45.LINK_TYPE_CODE = "Y" Else DSLink45.LINK_TYPE_CODE = "N"


Now all my rows were dropped and 0 rows were passes to the output file.
That is it is checking for the condition and dropping all rows.

Can you tell me where the exact problem is in my code?

Thanks
Kavuri

Hi,
I would like to be more clear, i want to have a condition in my job , which stage is best for me, I got the answer as Transformer stage. As I am trying to use it, my records are being dropped. I dont want to drop any record, just simply update a field with "Y" or "N".

Thanks
Kavuri

Wrong derivation. All you need is

If (DSLink45.LINK_TYPE_CODE = "ERPFO" Or DSLink45.LINK_TYPE_CODE = "HSPFO") Then "Y" Else "N"

This in the derivation field in the output link will do.

Hi maveric,
I tried with the derivation what you have given. It still dropping all the rows. I need all rows in my output with either "Y" or "N" in that field.
What can I do in this?

Thanks
Kavuri

Must be some other reason for the records being dropped. See if there is any meta data mismatch. what warnings/errors are you getting in the job log? Try comparing the strings with Compare() function.

If Compare(DSLink45.LINK_TYPE_CODE, "ERPFO") Or Compare(DSLink45.LINK_TYPE_CODE, "HSPFO") Then "Y" Else "N"

Derivation would look something like this. Not sure of the syntax though.

I placed like following

If (Compare(DSLink45.LINK_TYPE_CODE, "ERPFO") Or Compare(DSLink45.LINK_TYPE_CODE, "HSPFO")) Then "Y" Else "N"

Stll all the columns are getting dropped.

I am getting following warning

Transformer_54,7: Field 'LINK_TYPE_CODE' from input dataset '0' is NULL. Record dropped.


Thanks
Kavuri

Yes, I solved this issue.

If (NullToZero(DSLink45.LINK_TYPE_CODE) = "ERPFO" Or NullToZero(DSLink45.LINK_TYPE_CODE) = "HSPFO") Then "Y" Else "N"

i.e we need to place NullToZero, because input column is consistng of Null values.

Thanks to all who made me to debug this issue.

Kavuri