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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

delete first 7 characters in column in tMap

Hi, I just started with Talend and am learning a lot. But I have an issue I can't resolve and haven't found a solution to yet.

 

I have a table with combined info. It consists of postal code and address and I want it split.Example: "6093 EA Heythuysen". It has to split in "6093 EA" and "Heythuysen".

 

Via tMap I have added the column twice to the right side.

In the first I did a stringhandling.LEFT on the first 7 positions and this works. I get the 6093EA.

On the other output I can't use a stringhandling.RIGHT because I have different values with different lengths.

 

I tried substring(7) but then I get a string index out of range error.

 

How can I get rid of the first 7 characters?

 

Thank you in advance from a novice Talend user.

Labels (2)
1 Solution

Accepted Solutions
manodwhb
Champion II
Champion II

use this

StringHandling.LEN(row3.test)>7 ? (row3.test).substring(7,StringHandling.LEN(row3.test)): row3.test

View solution in original post

10 Replies
manodwhb
Champion II
Champion II

in tmap first check the length of the string like below.

 

StringHandling.LEN(row3.test)>7 ? (row3.test).substring(7,StringHandling.LEN(row3.test)): row3.test

Anonymous
Not applicable
Author

hi,

please find the below attachment and expression in tmap.

row3.Data.substring(0,7)   ===(For first Five)

row3.Data.substring(7,StringHandling.LEN(row3.Data))===(For Last Data)

 


Capture4.PNG
Capture5.PNG
Anonymous
Not applicable
Author

Hi,

 

The field has a length of 110. I can see this in the right bottom.

I've used the command (adres2 is the column):

StringHandling.LEN(row3.adres2)>7 ?substring(7,StringHandling.LEN(row3.adres2)):row3.adres2

 

Then I get this error:

Execution failed : Job compile errors
At least job "VBO_met_alle_functie" has a compile errors, please fix and export again.
Error Line: 6994
Detail Message: The method substring(int, int) is undefined for the type VBO_met_alle_functie
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

 

What am I doing wrong?

manodwhb
Champion II
Champion II

use this

StringHandling.LEN(row3.test)>7 ? (row3.test).substring(7,StringHandling.LEN(row3.test)): row3.test

TRF
Champion II
Champion II

Or just this one!

row3.test != null && row3.test.length() > 7 ? row3.test.substring(7) : row3.test
Anonymous
Not applicable
Author

Wow! What a great community in here!

Thank you for the help.

 

I've used this:

StringHandling.LEN(row3.adres2)>7 ? (row3.adres2).substring(7,StringHandling.LEN(row3.adres2)): row3.adres2 and it works.

 

Now a finale question. My understanding in Java is at novice level (I'm just learning it since last week). Do you care to explain how this statement is build?

manodwhb
Champion II
Champion II

first it will check the lenght is grater than 7 then it will do the substing else it will just pass the same value.

TRF
Champion II
Champion II

I prefer to explain mine as it is shorter:

row3.adres2 != null && row3.adres2.length() >= 7 ? row3.adres2.substring(7) : row3.adres2

If adres2 is nor null and its length is greater or equal to 7 characters, get 7 first characters, else get adres2 as it.

 

Anonymous
Not applicable
Author

Both Manohar and TRF, thank you for the solutions and explanation. Much appreciated!