Handling Nulls

[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.

[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

[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.

[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

[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?

[chulett]

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.

[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

[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,

[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))