One Star

If Statement: tMap Expression Builder

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!
21 REPLIES
One Star

Re: If Statement: tMap Expression Builder

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.
One Star

Re: If Statement: tMap Expression Builder

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?
One Star

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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

Re: If Statement: tMap Expression Builder

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?""Smiley Sadrow1.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 :-)
One Star

Re: If Statement: tMap Expression Builder

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

Re: If Statement: tMap Expression Builder

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?""Smiley Sadrow1.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 "=="?
One Star

Re: If Statement: tMap Expression Builder

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?
One Star

Re: If Statement: tMap Expression Builder

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
Moderator

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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?
One Star

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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

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

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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
One Star

Re: If Statement: tMap Expression Builder

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.
One Star

Re: If Statement: tMap Expression Builder

i have a question..
suppose my file have below format(file having different batches) having different schemas for each record type
5200ASDCRSFTHNJUYRE ACH INS PREM        9005900018CORINS. PREM.170315170317   1021000021441991
6270000028027524845075       0000000000094139985                              1021000021441991
798C01071000010027138      111900656878086567                         CHANGE63802E000021441991
6270000028027524845075       0000000000094139986                              1021000021441992
798C01071000010027139      111900656878086567                         CHANGE63802E000021441992
820000001000000014000000000000000000000000009005900018                         021000021441991
so I need to apply a logic to map out certain fields from different record types.How will I do it. plz help. how should I convert the format YYMMDD to MM/DD/YY in tmap. how to use looping to access this type of records bcoz record 6 and 7 come alternatively in each batch.
One Star

Re: If Statement: tMap Expression Builder

@Swiggy, could you open a new post with this subject?