One Star

[resolved] How to check values if they are "null" and replace them (...)

Hello everybody,
I wish everyone here a happy and talended new year! Smiley Wink
The year comes for me with the following question:
I want to replace empty values (they have the value "null") with a zero (0).
Firstly, a picture of the job tells much more:

In my TOS-job I connect values coming from an excel-sheet and an oracle-database. Before that happens in a tmap, I split the rows coming from the excel-sheet in a needed way.  
How can I find out, which cells, coming from that excel-sheet are "null"? Because not all of them are "null" and those who are, cause issues later, when I want to load the new data into another program. My problem would be solved, if I could implement a routine, which checks, if there is a value in a cell coming from the excel-sheet and if the result shows, that the value is "null", set a new value 0 (means "zero").
Can I implement that as a variable in the tmap? And how? Or is it better to use another component before the dataflow flows into the tmap, like tReplace?
I hope I described it understandable.
Does somebody of you experts have an idea for me? Smiley Happy
Best regards
Bexy

 
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hey everybody,
I think, I`ve found a solution:
I use this in the tmap to check the cells coming from the excel-sheet:
(Excel.Value1 == null) || ("".equals(Excel.Value1)) ? "0" : Excel.Value1
4 REPLIES
Seventeen Stars

Re: [resolved] How to check values if they are "null" and replace them (...)

Do you mean the String value "null" or the real null (nothing)?
Using a routine is the best method to deal with problematic content. This way you could develop the rule at one place and reuse it in various places. This is my favourite way to deal with such things. 
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hello Jlolling,
there is nothing written into some of the cells - so I think the answer to your question is "real null". These fields are empty.
So, do you have an idea for me? That would be great! Smiley Happy
Best regards
Bexy
One Star

Re: [resolved] How to check values if they are "null" and replace them (...)

Hey everybody,
I think, I`ve found a solution:
I use this in the tmap to check the cells coming from the excel-sheet:
(Excel.Value1 == null) || ("".equals(Excel.Value1)) ? "0" : Excel.Value1
Moderator

Re: [resolved] How to check values if they are "null" and replace them (...)

Hi,
Have you tried method:

row.ColumnName==null || row.ColumnName.isEmpty())?"Unknown":row.ColumnName(null string) in tMap?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.