Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Write Table now available in Qlik Cloud Analytics: Read Blog
cancel
Showing results for 
Search instead for 
Did you mean: 
_AnonymousUser
Specialist III
Specialist III

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!
Labels (2)
21 Replies
_AnonymousUser
Specialist III
Specialist III
Author

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?
_AnonymousUser
Specialist III
Specialist III
Author

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
Anonymous
Not applicable

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
0683p000009MADg.jpg 0683p000009MADl.jpg
Anonymous
Not applicable

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?
alevy
Specialist
Specialist

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
Anonymous
Not applicable

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.
Anonymous
Not applicable

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)
Anonymous
Not applicable

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
Anonymous
Not applicable

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
_AnonymousUser
Specialist III
Specialist III
Author

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.