topic Re: An issue with AND operator in an expression in Talend Studio

An issue with AND operator in an expression

MAlex1 — Sat, 16 Nov 2024 03:12:58 GMT

Hello everybody,

I am struggling with some issues in tMap expression. There is the table with those fields:

ANRED (the title of person) with those values:

MR	1
MISS	2
MRS	3
MS	4
DR	null

GESCH (gender key) with those values:

Female	2
Male	1

For the case when ANRED value is null I need to use this rule for output:

IF ANRED = null AND GESCH = 2 then output_value = 4 ELSE output_value = 1.

This is my expression:

(Basic_G2_Structure2.ANRED==null)&&(Basic_G2_Structure2.GESCH!="2")?"1":"4"

Unfortunately, the output from the expression is always '1' for all ANRED values although the values are correct on input.

I guess I have something wrong in the expression but I do not know what.

Could you someone advise me, please?

Thank you.

/Martin

Re: An issue with AND operator in an expression

TRF — Wed, 19 Feb 2020 21:08:47 GMT

Nevers use == or != to compare 2 strings, use String.equal() method instead.
(Basic_G2_Structure2.ANRED==null)&&(!"2".equals(Basic_G2_Structure2.GESCH))?"1":"4"

Re: An issue with AND operator in an expression

MAlex1 — Thu, 20 Feb 2020 13:00:43 GMT

Hi TRF,

thank you for your quick reply. Yes, the issue was caused by the incorrect expression - thanks for your advice! However, it still doesn't work as it should. It seems that not all of the context was clear from my post.

By the expression are changed all values which should be excluded for transformation. Here is an example. Let's have those five records in the input table (Basic_G2_Structure2😞

Basic_G2_Structure2

ID	ANRED	GESCH
100	1	1
101	4	2
103	null	1
104	2	2
105	null	2

By using the expression:

(Basic_G2_Structure2.ANRED==null)&&("2".equals(Basic_G2_Structure2.GESCH))?"1":"4"

I would expect this output in the output table (Final_G2_Structure)

Final_G2_Structure

ID	ANRED	GESCH
100	1	1
101	4	2
103	1	1
104	2	2
105	4	1

because the condition is ANRED=null AND GESCH=2 (both have to be TRUE).

Unfortunately, the output is the following:

Final_G2_Structure

ID	ANRED	GESCH
100	4	1
101	4	2
103	4	1
104	4	2
105	4	1

For some reason are impacted records which should be excluded from evaluation.

Thank you for your help again.

Re: An issue with AND operator in an expression

Ganshyam — Fri, 21 Feb 2020 13:40:53 GMT

Hi,

Condition in Tmap - Component

row1.ANRED.equals("")&& row1.GESCH.equals("1")?"1" :
row1.ANRED.equals("")&& !row1.GESCH.equals("1")?"4":row1.ANRED

Hope this solves your purpose.

Ganshyam

Re: An issue with AND operator in an expression

MAlex1 — Mon, 09 Mar 2020 16:52:16 GMT

Hi Ganshyam,

thank you for your reply. I am sorry for the late response but I was busy with another critical issue in the past two weeks.

I have tried to use your expression:

Basic_G2_Structure2.ANRED.equals("")&& Basic_G2_Structure2.GESCH.equals("1")?"1":Basic_G2_Structure2.ANRED.equals("")&& !Basic_G2_Structure2.GESCH.equals("1")?"4":Basic_G2_Structure2.ANRED

but the execution of the job already finishes with the error:

Starting job G2_Data_IT0002 at 17:40 09/03/2020.

[statistics] connecting to socket on port 3892
[statistics] connected
[FATAL]: xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002 - tMap_3 null
java.lang.NullPointerException
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tFileInputExcel_2Process(G2_Data_IT0002.java:10764)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tFileInputExcel_1Process(G2_Data_IT0002.java:2131)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tPrejob_1Process(G2_Data_IT0002.java:14596)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.runJobInTOS(G2_Data_IT0002.java:15015)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.main(G2_Data_IT0002.java:14683)
Exception in component tMap_3 (G2_Data_IT0002)
java.lang.NullPointerException
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tFileInputExcel_2Process(G2_Data_IT0002.java:10764)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tFileInputExcel_1Process(G2_Data_IT0002.java:2131)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.tPrejob_1Process(G2_Data_IT0002.java:14596)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.runJobInTOS(G2_Data_IT0002.java:15015)
at xxx_uk.g2_data_it0002_0_1.G2_Data_IT0002.main(G2_Data_IT0002.java:14683)
[statistics] disconnected
Job G2_Data_IT0002 ended at 17:40 09/03/2020. [exit code=1]

It looks the expression is returning a null value:

But maybe my listing of the expression is bad.

Thank you for your support.

Re: An issue with AND operator in an expression

manodwhb — Tue, 10 Mar 2020 03:33:43 GMT

@MAlex , below the below way.

("").equals(row1.ANRED)&& ("1").equals(row1.GESCH)?"1" :
("").equals(row1.ANRED)&& !("1").equals(row1.GESCH)?"4":row1.ANRED

Re: An issue with AND operator in an expression

MAlex1 — Tue, 10 Mar 2020 10:06:28 GMT

Hi Manohar,

thank you. The output on the console is without error now. Unfortunately, the result is not still met my expectations, my explanation is not enough accurate probably.

I have those values in the input (Basic_G2_Structure):

|=----+-----+-----+-----+-----+-----+-----+

and I need this output:

|=----+-----+-----+-----+-----+-----+-----+

So, some expression like this (pseudo-code):

IF ANRED = null AND IF GESCH = "1" THEN ANRED = "1" ELSE ANRED = "4"

Thank you for your support.

/Martin

Re: An issue with AND operator in an expression

manodwhb — Tue, 10 Mar 2020 10:12:49 GMT

@MAlex , try the below one and let me know.

(row1.ANRED==null || ("").equals(row1.ANRED))&& ("1").equals(row1.GESCH)?"1" :"4"

Re: An issue with AND operator in an expression

MAlex1 — Tue, 10 Mar 2020 17:49:32 GMT

Hi Manohar,

the result is better but another issue has appeared now.

Here is my input (Basic_G2_Structure):

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |1 |1 |

|P0002|42762|null |null |null |null |1 |

|P0002|43875|null |null |null |null |2 |

|=----+-----+-----+-----+-----+-----+-----+

here is the expression:

(Basic_G2_Structure2.ANRED==null || ("").equals(Basic_G2_Structure2.ANRED))&& ("1").equals(Basic_G2_Structure2.GESCH)?"1" :"4"

and here is the output after processing:

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |4 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|43875|null |null |null |4 |2 |

|=----+-----+-----+-----+-----+-----+-----+

So, for CLIID 42762 and 43875 is the result according to my expectation. But, unfortunately, for the CLIID 10201, the value for ANRED was changed to '4':

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |4 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|43875|null |null |null |4 |2 |

|=----+-----+-----+-----+-----+-----+-----+

although according to condition statement it should be excluded from processing because:

IF ANRED = null AND IF GESCH = "1" THEN ANRED = "1" ELSE ANRED = "4".

So, the correct output should be:

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |1 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|43875|null |null |null |4 |2 |

|=----+-----+-----+-----+-----+-----+-----+

Could you help me overcome this last obstacle, please?

Thank you.

/Martin

Re: An issue with AND operator in an expression

manodwhb — Wed, 11 Mar 2020 03:02:36 GMT

@MAlex ,if you want that you then your expression should as below .

(Basic_G2_Structure2.ANRED==null || ("").equals(Basic_G2_Structure2.ANRED)|| ("1").equals(Basic_G2_Structure2.ANRED))&& ("1").equals(Basic_G2_Structure2.GESCH)?"1" :"4"

Input

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |1 |1 |

|P0002|42762|null |null |null |null |1 |

|P0002|43875|null |null |null |null |2 |

|=----+-----+-----+-----+-----+-----+-----+

Output

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|10201|null |null |null |1 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|43875|null |null |null |4 |2 |

|=----+-----+-----+-----+-----+-----+-----+

Re: An issue with AND operator in an expression

MAlex1 — Wed, 11 Mar 2020 11:02:29 GMT

Hi Manohar,

the result is again better than previously. But still not good.

Here is current input (Basic_G2_Structure):

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|42730|null |null |null |1 |1 |

|P0002|42737|null |null |null |1 |1 |

|P0002|42762|null |null |null |null |1 |

|P0002|42768|null |null |null |3 |2 |

|P0002|42774|null |null |null |3 |2 |

|P0002|42782|null |null |null |3 |2 |

|P0002|42784|null |null |null |3 |2 |

|P0002|42789|null |null |null |4 |2 |

|P0002|42793|null |null |null |1 |1 |

|=----+-----+-----+-----+-----+-----+-----+

here is the expression:

(Basic_G2_Structure2.ANRED==null || ("").equals(Basic_G2_Structure2.ANRED)|| ("1").equals(Basic_G2_Structure2.ANRED))&& ("1").equals(Basic_G2_Structure2.GESCH)?"1" :"4"

and here is the output after processing:

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|42730|null |null |null |1 |1 |

|P0002|42737|null |null |null |1 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|42768|null |null |null |4 |2 |

|P0002|42774|null |null |null |4 |2 |

|P0002|42782|null |null |null |4 |2 |

|P0002|42784|null |null |null |4 |2 |

|P0002|42789|null |null |null |4 |2 |

|P0002|42793|null |null |null |1 |1 |

|=----+-----+-----+-----+-----+-----+-----+

In the case CLIID 42762 the expression works according to expectation:

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|42762|null |null |null |1 |1 |

|=----+-----+-----+-----+-----+-----+-----+

But for all cases where GESCH value before processing = 2 the ANRED value was changed from original to new value '4' after processing via the expression:

|=----+-----+-----+-----+-----+-----+-----+

|=----+-----+-----+-----+-----+-----+-----+

|P0002|42730|null |null |null |1 |1 |

|P0002|42737|null |null |null |1 |1 |

|P0002|42762|null |null |null |1 |1 |

|P0002|42768|null |null |null |4 |2 |

|P0002|42774|null |null |null |4 |2 |

|P0002|42782|null |null |null |4 |2 |

|P0002|42784|null |null |null |4 |2 |

|P0002|42789|null |null |null |4 |2 |

|P0002|42793|null |null |null |1 |1 |

|=----+-----+-----+-----+-----+-----+-----+

So, there is still something in the expression that causes change ANRED value despite the original ANRED value = null.

There is condition with logical AND:

IF ANRED = null AND IF GESCH = "1" THEN ANRED = "1" ELSE ANRED = "4"

so, both conditions have to match at the same time within the evaluation.

Thanks a lot for your support again.

/Martin

Re: An issue with AND operator in an expression

manodwhb — Wed, 11 Mar 2020 11:13:00 GMT

@MAlex , can you try with below expression.

(Basic_G2_Structure2.ANRED==null && ("1").equals(Basic_G2_Structure2.GESCH))?"1" :"4"

Re: An issue with AND operator in an expression

Ganshyam — Wed, 11 Mar 2020 12:22:10 GMT

Hello @MAlex ,

You require below results?

Regards

Re: An issue with AND operator in an expression

MAlex1 — Wed, 11 Mar 2020 13:14:23 GMT

Hi Manohar,

with this expression:

(Basic_G2_Structure2.ANRED==null && ("1").equals(Basic_G2_Structure2.GESCH))?"1" :"4"

we have returned back to begin:

input Final_G2_Structure

ID	ANRED	GESCH
100	1	1
101	4	2
103	1	1
104	2	2
105	4	1

Unfortunately, the output is the following:

output Final_G2_Structure

ID	ANRED	GESCH
100	4	1
101	4	2
103	4	1
104	4	2
105	4	1

However, thank you for your help.

/Martin

Re: An issue with AND operator in an expression

MAlex1 — Wed, 11 Mar 2020 13:18:11 GMT

Hello Ganshyam,

thank you for your interest. Really, it looks like the output from the expression which I am looking for …

/Martin

Re: An issue with AND operator in an expression

MAlex1 — Fri, 13 Mar 2020 09:55:38 GMT

Hi Ganshyam,

any idea how to get the output on your screenshot via an expression?

Thank you.

/Martin

Re: An issue with AND operator in an expression

Ganshyam — Fri, 13 Mar 2020 13:21:39 GMT

Hello @MAlex

I tried below condition to get your expected result.Try it out and validate against your input data.

(row1.ANRED.equals("null")|| row1.ANRED.equals("1"))&& row1.GESCH.equals("1")?"1" :
row1.ANRED.equals("null")&& !row1.GESCH.equals("1")?"4":row1.ANRED

Regards

Re: An issue with AND operator in an expression

MAlex1 — Fri, 13 Mar 2020 14:22:40 GMT

Hello Ganshyam,

thanks a lot! Your solution is OK, just small adjustment was necessary:

(row1.ANRED==null || row1.ANRED.equals("1"))&& row1.GESCH.equals("1")?"1": row1.ANRED==null&& !row1.GESCH.equals("1")?"4":row1.ANRED

Thank you again.

/Martin