Handling Nulls
Moderators: chulett, rschirm, roy
Handling Nulls
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.
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.
Kalpana Marupudi
-
- Participant
- Posts: 40
- Joined: Tue Nov 11, 2008 5:49 am
Re: Handling Nulls
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
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
Knowledge is Fair,execution is matter!
Re: Handling Nulls
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.
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.
Kalpana Marupudi
-
- Participant
- Posts: 40
- Joined: Tue Nov 11, 2008 5:49 am
Re: Handling Nulls
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
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
Knowledge is Fair,execution is matter!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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:
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,
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
Regards,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.