new column

kavuri · Post by **kavuri** » Tue Sep 11, 2007 7:14 am

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

Maveric · Post by **Maveric** » Tue Sep 11, 2007 7:29 am

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

kavuri · Post by **kavuri** » Tue Sep 11, 2007 8:34 am

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

Maveric · Post by **Maveric** » Tue Sep 11, 2007 8:40 am

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.

kavuri · Post by **kavuri** » Tue Sep 11, 2007 8:55 am

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

Maveric · Post by **Maveric** » Tue Sep 11, 2007 9:18 am

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.

priyadarshikunal · Post by **priyadarshikunal** » Tue Sep 11, 2007 9:18 am

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:

Code: Select all

STAY_LINK_FACT.RELATED_SERVICE_ID is null and other conditions as you mentioned.

kavuri · Post by **kavuri** » Tue Sep 11, 2007 11:41 am

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

kavuri · Post by **kavuri** » Tue Sep 11, 2007 3:07 pm

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

Maveric · Post by **Maveric** » Wed Sep 12, 2007 1:15 am

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.

kavuri · Post by **kavuri** » Wed Sep 12, 2007 7:01 am

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

Maveric · Post by **Maveric** » Wed Sep 12, 2007 7:08 am

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.

kavuri · Post by **kavuri** » Wed Sep 12, 2007 7:25 am

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

kavuri · Post by **kavuri** » Wed Sep 12, 2007 8:34 am

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