topic Re: If Statement: tMap Expression Builder in Talend Studio

If Statement: tMap Expression Builder

_AnonymousUser — Sat, 16 Nov 2024 13:59:58 GMT

I am trying to extract data from a database and build an expression to convert into the correct ouptut format, but i am having difficulty. My project is in Java.
The data is stored as 'string' values in the input database and is in the format:
CISR_VER
2605
2709
0
2803
3001
0
3002
Where the first 2 digits relate to the year (from a base of 1980) and the last two digits relate to the period/month (1 to 12). Therefore 2605 := Year 2006, Period 05...and 3002 := Year 2010, Period 02 and a 0 is blank or no data held.
The output i am trying to achieve is a column in the format
05/2006
09/2007
null or blank
03/2008
01/2010
null or blank
etc....
I have a tMap that trims the last two digits and stores them in Var.Month (i can use the: Integer.parseInt(Var.Month) to convert to an integer)
I have another variable Var.Year that takes the first two digits, converts them into Integer format and adds 1980.
My problem is that where there is a zero in the input source i am getting a 1980 in the output where i need it to be blank/null. I have tried to build an IF function against the expression but cant seem to get the correct syntax.
Is there an easier way to do this in one expression that converts the input to an integer and can trim the digits and add the 1980 base year and add the '/'??
Any help much appreciated!

Re: If Statement: tMap Expression Builder

Anonymous — Tue, 14 Apr 2009 15:47:15 GMT

I'm absolutly not sure that it is your problem but if you mean that you tried to insert an IF statement in the expression box, it won't work because the code isn't generated for it. You should create a new routine with the same java code and then it will work.

Re: If Statement: tMap Expression Builder

_AnonymousUser — Tue, 14 Apr 2009 20:10:40 GMT

Basically all i need is the correct format of an If expression to test of Var.Year is 0 then dont insert anything into the tMap output column if Var.Year is greater than 0 then add 1980 to the value and insert the value in the output.
Any Ideas?

Re: If Statement: tMap Expression Builder

c0utta — Wed, 15 Apr 2009 03:50:13 GMT

Hi Andrew,
Use a tJavaRow to take your incoming data flow and use the following:

if(Integer.parseInt(input_row.CISR_VER) == 0)
{
	output_row.CISR_VER = "";
}
else
{
	Integer year = Integer.parseInt(input_row.CISR_VER.substring(0, 2));
	
	year += 1980;
	output_row.CISR_VER = input_row.CISR_VER.substring(2, 4) + "/" + year.toString();
}

Note that you could do this inline within a tMap expression, but I've expanded it for clarity here.
Cheers,
c0utta

Re: If Statement: tMap Expression Builder

_AnonymousUser — Wed, 15 Apr 2009 13:28:21 GMT

Many Thanks c0utta!
Worked like a charm. I didnt know that a tJavaRow was even possible.

Re: If Statement: tMap Expression Builder

Anonymous — Wed, 18 Aug 2010 14:33:13 GMT

I am trying to use a Var during using a tMap for this and getting an error:
'Syntax error on token(s), misplaced construct(s)'
This is my structure:
OUT_Table:
id
name
OUT_Table.name -> Var where this is being called
if(Integer.parseInt(name.OUT_Table) == 0) {
Var.var1 = null;
} else {
Var.var1 = name.OUT_Table;
}
Then it will go out of the that Var and into the IN_Table
When I try to run this, i get the error: 'Syntax error on token(s), misplaced construct(s)'
Thanks.

Hi Andrew,
Use a tJavaRow to take your incoming data flow and use the following:
if(Integer.parseInt(input_row.CISR_VER) == 0)
{
	output_row.CISR_VER = "";
}
else
{
	Integer year = Integer.parseInt(input_row.CISR_VER.substring(0, 2));
	
	year += 1980;
	output_row.CISR_VER = input_row.CISR_VER.substring(2, 4) + "/" + year.toString();
}
Note that you could do this inline within a tMap expression, but I've expanded it for clarity here.
Cheers,
c0utta

Re: If Statement: tMap Expression Builder

_AnonymousUser — Thu, 12 May 2011 10:31:50 GMT

c0utta, i don't understant your affermation: "Note that you could do this inline within a tMap expression, but I've expanded it for clarity here."
I've tryed tjavaRow component and it accepts the common if java statement.
In tMap component in the Expression I'm able only to use the java statement -> Condizion?True:False.. the if statement isn't compiled..
It's right?

Re: If Statement: tMap Expression Builder

Anonymous — Wed, 18 May 2011 16:38:14 GMT

Yes but you can use the java statement to do it, just put that in the Expression of your output column :
Integer.parseInt(row1.CISR_VER) == 0?"" row1.CISR_VER.substring(2,4) + "/" + ((Integer)(Integer.parseInt(row1.CISR_VER.substring(0, 2)) + 1980)).toString())
It's a little bit more tricky I agree 🙂

Re: If Statement: tMap Expression Builder

janhess — Wed, 18 May 2011 16:45:39 GMT

Why not just have
row1.CISR_VER.equals("0")?"" row1.CISR_VER.substring(2,4) + "/" + ((Integer)(Integer.parseInt(row1.CISR_VER.substring(0, 2)) + 1980)).toString())

Re: If Statement: tMap Expression Builder

Anonymous — Fri, 29 Mar 2013 15:12:59 GMT

Yes but you can use the java statement to do it, just put that in the Expression of your output column :
Integer.parseInt(row1.CISR_VER) == 0?""row1.CISR_VER.substring(2,4) + "/" + ((Integer)(Integer.parseInt(row1.CISR_VER.substring(0, 2)) + 1980)).toString())
It's a little bit more tricky I agree 🙂

"==" sintax does not work.
".equals" sintax does work.
Why do you suggest "=="?

Re: If Statement: tMap Expression Builder

_AnonymousUser — Mon, 01 Apr 2013 21:35:01 GMT

This seems close to what I need to do and for some reason I can't quite massage it for my purposes.
Our records coming in have a net available and warehouse.
Example
Product | Warehouse | Qty
---------------------------------------------
Gizmo | Paris | 3
Gizmo | London | 7
Widget | Paris | 6
Widget | London | 4

The inventory record in the outbound table has a field for each warehouse which contains the qty available at that location.
Example:
Product | Paris | London
-----------------------------
Widget | 6 | 4
Gizmo | 3 | 7
I want to populate the Paris field with inventory from Paris and the London field with inventory from London. (And no, I did NOT design this stupid table)
So I have something like this in the expression in my tmap
if (row2.whse=="Paris) inventory.Paris = row2.netavailable
but it doesn't like it.
Where am I going wrong?

Re: If Statement: tMap Expression Builder

_AnonymousUser — Tue, 02 Jul 2013 18:02:15 GMT

Hi Everyone,
I have a text "tab" delimited file in which i have data as per the below given syntax:
"PCS" "CAMPAIGN_CYCLE_ID" "TREATMENT_ID"
4803000000 13143 1063362
6143000000 13143 1063362
8136000000 13143 1063361
6264000000 13143 1063361
I would like to make two text files with PCS having unique TREATMENT_ID that will be something like this:
File1
"PCS" "CAMPAIGN_CYCLE_ID" "TREATMENT_ID"
4803000000 13143 1063362
6143000000 13143 1063362
File2
"PCS" "CAMPAIGN_CYCLE_ID" "TREATMENT_ID"
8136000000 13143 1063361
6264000000 13143 1063361
I have the input file in place in my job, then i have tmap but i cant write the concern code in it, so that i have two different files for PCS with uniques Treatment_ID.
Your help will be a savior.
Thanks

Re: If Statement: tMap Expression Builder

Anonymous — Wed, 03 Jul 2013 04:12:47 GMT

Hi Abhimanyu,
Do you want to separate your input source into two output files depending on the different "TREATMENT_ID"? If so, you can set a filter in tMap.
See my screenshots for details
In addition, there are related articles about tMap+operation, tMap and tMap+Job+example, hope it will be useful for your further job.
Best regards
Sabrina

Re: If Statement: tMap Expression Builder

Anonymous — Fri, 09 Aug 2013 14:43:04 GMT

I'm also having problems with the tMap and expressions. My Java isn't all that great but I passed it to a colleague and he thinks the actual Java should be fine.
I'm attempting a simple ETL job to better clean the data for easier profiling (the DB is a bit of a mess) and I was using a tMap with expressions such as:

if (row1.EanCode == null && row1.EAN_Required == 'N') {
	Out.EanCode = 'Not Required';
} else {
	Out.EanCode = row1.EanCode;
}

and:

if (row1.Weight.compareTo(BigDecimal.ZERO) == 0 && row1.Weight_Required == 'N') {
	Out.Weight = 0;
} else if (row1.Weight.compareTo(BigDecimal.ZERO) == 0 && row1.Weight_Required == 'Y') {
	Out.Weight = null;
} else {
	Out.Weight = row1.Weight;
}

and finally:

if (Out.EanCode != null && Out.Supplier_Product_Code != null && Out.Weight != null && Out.Single_Length != null) {
	Out2.Data_Quality_Level = "Blue";
} else {
	Out2.Data_Quality_Level = "Red";
}

I'm getting "Syntax error on token(s), misplaced construct(s)", "Invalid character constant" and "Syntax error on token '=', Expression expected after this token" errors back when I run this job although when I look at the underlying code that Talend generates it appears to be finding some fault with some of the code it has generated that precedes my conditional code:

Out_tmp.EanCode = if (row1.EanCode == null && row1.EAN_Required == 'N') {
	Out.EanCode = 'Not Required';
} else {
	Out.EanCode = row1.EanCode;
} ;

With a syntax error on the "Out_tmp.EanCode =" part.
What do I need to do to get this to work?

Re: If Statement: tMap Expression Builder

alevy — Sun, 11 Aug 2013 03:56:57 GMT

if (row1.EanCode == null && row1.EAN_Required == 'N') {
Out.EanCode = 'Not Required';
} else {
Out.EanCode = row1.EanCode;
}
is the Java syntax you would use if writing your own code e.g. in a tJava or tJavaRow.
But tMap already generates the "Out.EanCode = " part of the statement for you. What you provide as the tMap expression is only what appears on the right-hand side of that statement (excluding the semi-colon) e.g. "Not Required" or row1.EanCode. That is why you're seeing code like:
Out_tmp.EanCode = if (row1.EanCode == null && row1.EAN_Required == 'N') {
Out.EanCode = 'Not Required';
} else {
Out.EanCode = row1.EanCode;
} ;
but that is not valid syntax since you can't have an assign statement or if conditions inside an assign statement.
To use if-then-else logic in a tMap expression, the correct syntax is:
row1.EanCode == null && row1.EAN_Required == 'N' ? 'Not Required' : row1.EanCode

Re: If Statement: tMap Expression Builder

Anonymous — Wed, 21 Aug 2013 09:23:02 GMT

Ah that makes more sense. I'm still not used to being able to see the code that Talend is generating and I know very little about Java right now so still get caught up on things.
Thanks for pointing out that I should be using ternarys in the tMap.

Re: If Statement: tMap Expression Builder

Anonymous — Tue, 24 Nov 2015 13:53:26 GMT

Hola a todos:
Soy nuevo en talend y estoy enfrentando un error el cual no he podido resolver,
Estoy usando un Tmap para hacer una sentencia IF, mi sentencia es
if(StringHandling.UPCASE(StringHandling.LEFT(row3.Linea_Limpia,8))=="USERNAME") row4.Usuario="SI"; else row4.Usuario="NO";
Donde row3.Linea_Limpia es la ENTRADA al Tmap y la SALIDA esta en row4.Usuario. La sentencia la estoy colocando en la expresion de row4 el cual corresponde a la salida
cuando lo compilo me sale este error, Syntax error on token(s), misplaced construct(s)

Re: If Statement: tMap Expression Builder

Anonymous — Wed, 25 Nov 2015 08:11:13 GMT

Hi Williampineda11,
This is an international forum and English is the language we use. Posting in English will allow you to get more visibility and more help. Thanks for your understanding!

Best regards
Sabrina

Re: If Statement: tMap Expression Builder

Anonymous — Tue, 26 Apr 2016 13:23:11 GMT

IIF ( (ISNULL(LE_CD) AND (MD_SOURCE_SYSTEM_O='15' OR MD_SOURCE_SYSTEM_O ='16')), OPERATING_UNIT_ID,
IIF ( (ISNULL(LE_CD) AND (MD_SOURCE_SYSTEM_O = '25' OR MD_SOURCE_SYSTEM_O = '28' OR MD_SOURCE_SYSTEM_O = '27')),
IIF(INSTR(OPERATING_UNIT_NAME_INV_LINE,'-',1) > 0, SUBSTR(OPERATING_UNIT_NAME_INV_LINE,1,(INSTR(OPERATING_UNIT_NAME_INV_LINE,'-',1)- 1))),
LE_CD))---how to write this in talned

Re: If Statement: tMap Expression Builder

_AnonymousUser — Fri, 17 Feb 2017 12:45:57 GMT

Hi,
For null check I need to add manually the condition to each and every column in tmap. I find it is difficult if we have 100+ columns.
So can any one suggest how to add null check globally for all the columns. Is talend have that feature?
I am in urgent with req. Please help.