topic Re: How to map data in multiple rows to a single record in a table in Talend Studio

How to map data in multiple rows to a single record in a table

sheftalenduser — Thu, 04 Oct 2018 11:22:40 GMT

Hello everyone

I have a query that returns between 1 and 8 rows; Within each row is a column value that I want to map to a table. The columns I want to map to are:
PREREQUISITE_UNIT_CODE1
PREREQUISITE_UNIT_CODE2
PREREQUISITE_UNIT_CODE3
PREREQUISITE_UNIT_CODE4
PREREQUISITE_UNIT_CODE5
PREREQUISITE_UNIT_CODE6
PREREQUISITE_UNIT_CODE7
PREREQUISITE_UNIT_CODE8

and the source data is:

In this example there are 5 rows so row 1 with mmb_seq = 1 should be mapped to PREREQUISITE_UNIT_CODE1; the row with mmb_seq = 2 should be mapped to PREREQUISITE_UNIT_CODE2 and so on.

The job is as below. The table should be updated with values in the prereq_unit_code column.

The tMap is as below:

The expression that maps the row to the column takes the form:

(PrerequisiteUnit.mmb_seq.equals("001") ?
PrerequisiteUnit.prereq_unit_code : "")

The mmb_seq check increments for each expression up to "008".

The job runs but only the last column in the target table is updated (PREREQUISITE_UNIT_CODE5); No other columns are populated. If I split the lookup table into 5 lookups, filitering on the mmb_seq value then the job works but this isn't practical as I have a number of categories of data like this so would lead to a proliferation of lookups.

Hope this makes sense - Andrew

Re: How to map data in multiple rows to a single record in a table

Anonymous — Thu, 04 Oct 2018 12:30:42 GMT

hey there,

I also wanted to know that hpw to map data in multiple rows without being confused and easy to accessible.

Thanks for sharing these information and images. I just want to ask, Will somebody guide me personally???

Re: How to map data in multiple rows to a single record in a table

Anonymous — Sat, 06 Oct 2018 05:53:23 GMT

hey there, I also wanted to know that hpw to map data in multiple rows without being confused and easy to accessible. Thanks for sharing these information and images. I just want to ask, Will somebody guide me personally ???

Re: How to map data in multiple rows to a single record in a table

sheftalenduser — Mon, 15 Oct 2018 14:13:03 GMT

Hi again,

Having failed to tempt anyone to offer a solution I've finally come up with one though it's not very elegant.

I've created a subjob that runs first to acquire the source data and associated lookup data as below.

The code for the tJavaFlex is as follows:

The code for the Java method is below

public static String getMapValue(Object obj, String seq) {
		@SuppressWarnings("unchecked")
		HashMap<String, String> map = (HashMap<String, String>)obj;
		String ret = "";
		ret = map.get(seq);
		return ret;
	}

The method lookup is done in the tMap as below:

Module.getMapValue(globalMap.get("locationsMap"), "001")

Any comments/alternative approaches are welcome.

Andrew

Talend-map-rows-to-columns-tjavaflex-job.PNG