topic Re: Only getting last value in Job in Talend Studio

Only getting last value in Job

rwnetwork — Mon, 30 Mar 2020 12:14:07 GMT

Good Morning Community,

Can anyone see why I am only getting the last value in a list of 170 products?

So for example, in the list below, I am only getting the X.

...

Please anyone, this is a blocker on a deadline. Thank you Community!

Re: Only getting last value in Job

billimmer — Mon, 30 Mar 2020 15:44:39 GMT

It's because you have a "component ok" connecting your tjava instead of an iterate.

Re: Only getting last value in Job

rwnetwork — Mon, 30 Mar 2020 16:00:04 GMT

HI billimmer!

Thank you.

Yes, I actually found that shortly after posting that using iterate allowed all 170 products to pass through to my tjava.

However, now I have another issue. I have an input component that needs to recognize these 170 products in its SQL query, however, it is not. It's still only recognizing that last product again in my 32 row output file.

Thoughts?

Here is the query in the blue highlighted input component:

select a, b, c

from table

where

fiinst in '" + globalMap.get("fiinst") + "'

group by a, b, c

Re: Only getting last value in Job

billimmer — Mon, 30 Mar 2020 16:20:58 GMT

are the other lookup's limiting the data? What exactly is stored in globalMap.get("fiinst")? If you run just the query by itself does it return the expected result?

Re: Only getting last value in Job

rwnetwork — Mon, 30 Mar 2020 16:31:35 GMT

The lookups are not limiting the data because if I remove the global variable syntax and hard code one of the product values (bolded below), I get the expected results.

For example:

select a, b, c

from table

where

fiinst in (‘06M’,’07M’)

group by a, b, c

I'm trying to creating a global variable so that the sql query only produces data for the 170 products which has a column name of fiinst.

So my tflowtoiterate looks like this:

Something is preventing my input step to recognize all 170 products....

Re: Only getting last value in Job

billimmer — Mon, 30 Mar 2020 17:28:53 GMT

if you add a tjava with the following:

System.out.println((String)globalMap.get("fiinst"));

What do you see for the contents of your variable?

Re: Only getting last value in Job

rwnetwork — Mon, 30 Mar 2020 17:57:10 GMT

Just getting dups of each of the products. So now I have 340 variables in the print screen area. But it says 170 execs on the iterate.

Re: Only getting last value in Job

billimmer — Mon, 30 Mar 2020 20:58:23 GMT

did you put System.out.println((String)globalMap.get("fiinst")); in it's own tJava component? or did you put it in tJavaRow?

It should be in tJava. does it show 170 different values in a single string, with each product separated by a comma?

It would help if you posted the code in your tJavaRow also...

Re: Only getting last value in Job

rwnetwork — Mon, 30 Mar 2020 22:26:51 GMT

I put the code in the tjava component like below.

Then a cross section of my results are below.

There are no commas separating the products.

Re: Only getting last value in Job

billimmer — Tue, 31 Mar 2020 14:17:19 GMT

In your query you had:

select a, b, c

from table

where

fiinst in '" + globalMap.get("fiinst") + "'

group by a, b, c

which doesn't work... And you had

select a, b, c

from table

where

fiinst in (‘06M’,’07M’)

group by a, b, c

Which does work.

So your variable globalMap.get("fiinst") should contain opening an closing parentheses, and single quotes around each part number, and include a comma between each part number or your query won't work - correct?

Re: Only getting last value in Job

rwnetwork — Tue, 31 Mar 2020 15:57:06 GMT

I've never had to call a global variable like this, so let me try your suggestions and advise.

Thank you!

Re: Only getting last value in Job

rwnetwork — Tue, 31 Mar 2020 16:04:50 GMT

Forgive me if I'm not understanding, but with your suggestion, how would I put single quotes around each part number when fiinst is supposed to represent the 170 products?

--So your variable globalMap.get("fiinst") should contain opening an closing parentheses, and single quotes around each part number, and include a comma between each part number or your query won't work - correct?

Sorry I'm not understanding! I thank you for being so helpful.

Re: Only getting last value in Job

billimmer — Tue, 31 Mar 2020 16:42:50 GMT

I would use a row#->tJavaFlex instead of iterate->tJava. It has a Start/End code section.

In the Start Code use:
// Initialize string variable called "parts"
String parts = "";

In the Main code use:
// Add each part to the "parts" string. Include single quotes and comma.

// REPLACE THE # CHARACTER WITH YOUR CONNECTION NUMBER

// NOTE: Also, include logic to skip parts you don't want.
parts = parts + "'" + row#.part + "',"

In the End Code use:
// We are done, so remove the last comma.
if (parts.length() > 0) {
parts = parts.substring(0, parts.length() - 1);
}

// Now add the parentheses
parts = "(" + parts + ")"

// Check your work to make sure it's correct. Comment out for production
System.out.println("My parts string: " + parts);

// Put the result in a global variable for use later in your query
globalMap.put("fiinst"", parts);

Re: Only getting last value in Job

rwnetwork — Tue, 31 Mar 2020 16:51:52 GMT

mmmmm ok, thanks so much billimmer! tjavaflex is also new to me so let me give it a try!

Again thank you, I would give you an elbow bump if I could right now.

Re: Only getting last value in Job

billimmer — Tue, 31 Mar 2020 17:09:39 GMT

Thanks hope it works for you.

This will get what you asked for, but you might also want to instead try writing the list of parts to a file or DB temporary table. This would be a more scalable solution, as there is a limit to the number of values that can be put into the "IN" statement of your query. So for example you would use:

tFilterRow, with the "Advanced" option and put your filter java code there. Then connect the tFilter to a tFileOoutputDelimited, or DB component. Then you can use this with a join or lookup later in the job.

Re: Only getting last value in Job

rwnetwork — Tue, 31 Mar 2020 17:17:34 GMT

mmmm this looks doable as well. I feel like I've gone crazy trying to get this to work!

Thank you again for your suggestions and quick responses.

Re: Only getting last value in Job

rwnetwork — Tue, 31 Mar 2020 19:05:36 GMT

Well billimmer

I have figured out the culprit and almost embarrassed to share. But want to help out the community if anyone else runs into this issue.

Maybe I've been cooped up for too long!

You were on the right path in your original suggestion. I had a pipe delimiter in my filtered .txt file, so therefore, the global variable was not reading the separation of the 170 products (only the last one in the list). I changed the pipe delimiter to a comma and voila!, my output is coming through as expected.

Thanks again for all your help!

Michelle