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

Five Stars

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

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: 

Talend-map-rows-to-columns-data.PNG

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.

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

The tMap is as below:

Talend-tmap-rrows-to-columns.PNG

 

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

Four Stars

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

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

Four Stars

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

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

Five Stars

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

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.

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

The code for the tJavaFlex is as follows:

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

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

 

 

 

Four Stars

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

This will give you two output rows for each input row and you can map the lookup table value to your type field and the relevant phone number depending on the lookup value Telldunkin.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog