Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
kumar444
Premium Member
Posts: 62 Joined: Wed Jan 12, 2011 10:01 am
Post
by kumar444 » Wed Oct 05, 2011 1:00 am
Code: Select all
If (Column_1)='0004400' And (column_2)='1221' And Index(column_3,"NOG",1) <>'0' Then 1 Else 0
Output:
column1 column2 column3
0004400 1221 DONOG
0004400 1221 NOG-NEW
Eliminate all other cases
Hi I have a requirement where i need to reject null values and empty spaces as well for all the three columns shown in the code.
The Index function here indicates that if a substring "NOG" exists then output the row.
I really appreciate your help.
-------------------------------------------
Kumar
vishal_rastogi
Participant
Posts: 47 Joined: Thu Dec 09, 2010 4:37 am
Post
by vishal_rastogi » Wed Oct 05, 2011 1:37 am
i think you can use the combination of isnotnull and len(trim(column))<>0
Vish
SURA
Premium Member
Posts: 1229 Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney
Post
by SURA » Wed Oct 05, 2011 1:59 am
vishal_rastogi wrote: i think you can use the combination of isnotnull and len(trim(column))<>0
len(trim(caol)) <> '' is right for char and not sure about 0.
DS User
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Wed Oct 05, 2011 2:52 am
You don't need
If expr Then 1 Else 0 because, in a Boolean context,
expr returns 1 (True) or 0 (False). Use any technique you like to handle null.
Code: Select all
(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1) <>'0')
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
BI-RMA
Premium Member
Posts: 463 Joined: Sun Nov 01, 2009 3:55 pm
Location: Hamburg
Post
by BI-RMA » Wed Oct 05, 2011 3:46 am
The only question left is: why compare the result of the Index-function to a string (should be int32).
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
ray.wurlod
Participant
Posts: 54607 Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Post
by ray.wurlod » Wed Oct 05, 2011 3:18 pm
Good point. The joys of copy-and-paste. It will work as a string, but require an unnecessary data type conversion. Better is:
Code: Select all
(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1) <> 0)
And even better is:
Code: Select all
(NullToEmpty(Column_1)='0004400') And (NullToEmpty(column_2)='1221') And (Index(NullToEmpty(column_3),"NOG",1))
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.