Page 1 of 1
Handling Nulls
Posted: Fri Feb 04, 2011 1:17 am
by kalpanam
HI all,
I have issue in handling NULLS..here is the problem description:
I'm having input data as:
SHOP_ID,SHOP_DESC,DEPT_ID,DEPT_DESC
123,asdf,456,lkjh
234,lkjhg,NULL,NULL
NULL,NULL,NULL,NULL
NULL,NULL,987,mnbv
NULL,NULL,NULL,NULL
I need to filter out all the null values records and only need to output the records with all columns with values and any single column with value.
please suggest me how to do it or let me know if you did not get the question correct.
Thanks & Regards,
Kalpana.
Re: Handling Nulls
Posted: Fri Feb 04, 2011 1:30 am
by ramsubbiah
Hi kalpana,
use the following the derivation in the transformer constraint
CAPTURING THE VALID RECORDS
ISNOTNULL(SHOP_ID) AND ISNOTNULL(SHOP_DESC) AND ISNOTNULL(DEPT_ID) AND ISNOTNULL(DEPT_DESC)
CAPTURING THE INVALID RECORDS:
ISNULL(SHOP_ID) OR ISNULL(SHOP_DESC) OR ISNULL(DEPT_ID) OR ISNULL(DEPT_DESC)
other way is create stage variable and include the above conditions and use the stage variable in the constraints
Thanks,
Ram.S
Re: Handling Nulls
Posted: Fri Feb 04, 2011 1:46 am
by kalpanam
First Thanks for your quick response...
I'm not getting what u said...
I need the output as
123,asdf,456,lkjh
234,lkjhg,NULL,NULL
NULL,NULL,987,mnbv to one output and
NULL,NULL,NULL,NULL
NULL,NULL,NULL,NULL to another dataset.How can i perform this by using transformer stage?
Thanks & Regards,
kalpana.
Re: Handling Nulls
Posted: Fri Feb 04, 2011 1:53 am
by ramsubbiah
In transformer create two output links for two datasets, inside transformer in the output link include the above two transformation
link1 ---> ISNOTNULL(SHOP_ID) AND ISNOTNULL(SHOP_DESC) AND ISNOTNULL(DEPT_ID) AND ISNOTNULL(DEPT_DESC) for getting the below output
NULL,NULL,NULL,NULL
NULL,NULL,NULL,NULL
link2 --> ISNULL(SHOP_ID) OR ISNULL(SHOP_DESC) OR ISNULL(DEPT_ID) OR ISNULL(DEPT_DESC) for getting the below output 123,asdf,456,lkjh
234,lkjhg,NULL,NULL
NULL,NULL,987,mnbv
i hope you know how to add a constraint in the transformer output link
Thanks,
Ram.S
Posted: Fri Feb 04, 2011 3:04 am
by ray.wurlod
Your specification is hard to understand. Presumably you must always output four fields. So what do you want to use to replace NULL where it occurs? Also is your source a text file or something else?
Posted: Fri Feb 04, 2011 8:17 am
by chulett
![Exclamation :!:](./images/smilies/icon_exclaim.gif)
You two -
ramsubbiah &
kalpanam - need to
stop quoting everything when you reply. There is a perfectly lovely
Reply to topic button down there, please use that unless there is a burning need to quote something.
Posted: Fri Feb 04, 2011 9:50 am
by ThilSe
If I understand your question correctly, set a variable with the count of number of nulls and output records that has count value of 1 or 4.
Eg A = IsNotNull(Col1)+...+IsNotNull(Coln)
Output record if A=1 or 4 (since you need to output if any one column or all four column has values)
Regards,
Senthil
Posted: Fri Feb 04, 2011 10:07 am
by jwiles
One item that would help you (and anyone else attempting to develop code/jobs/etc) is to first work out the logic of what you're trying to accomplish.
Using your original problem as the base, the logic essentially comes down to this:
Code: Select all
If any input column is Non-NULL, then
keep the record
else ## all columns are NULL
reject the record
From this basic logic (the above is called pseudo-logic), you can then proceed to how to accomplish that in DataStage or any other tool you happen to be using.
Regards,
Posted: Sat Feb 05, 2011 3:19 am
by Ravi.K
Write the below condition at Constraint for good records otherwise reject the records to a seperate stream.
(IsNotNull(SHOP_ID) Or IsNotNull(SHOP_DESC) Or IsNotNull(DEPT_ID) Or IsNotNull(DEPT_DESC))