topic Re: Looping through multiple columns Tmap in Talend Studio

Looping through multiple columns Tmap

RVeitch_84 — Fri, 15 Nov 2024 21:05:52 GMT

I have a table with over 89 columns of "meter types", see example below. I have to read each meter type and populate it in another table along with a meter type definition.

Record Type	Location	Seq Number	Date/Time	Serial #	Station #	meter1	meter2	meter3
4	abc	1	1/24/2024	1234	11	5
4	abc	2	1/24/2024	1234			10
4	abc	3	1/24/2024	1234				25

I have to query that table and populate the data in a different format where 'END_VALUE' is the value of the meter type.

SERIALNO	SET_ID	METER_TYPE	EXTERNAL_ID	END_VALUE	LOCATION
1234	123_abc	meter1	123_abc_meter1	5	abc
1234	123_abc	meter2	123_abc_meter2	10	abc
1234	123_abc	meter3	123_abc_meter3	25	abc

I can do this be creating 89 sub jobs and querying each meter type and then populating that column, but just looking for another option.

Thanks for any help.

Re: Looping through multiple columns Tmap

Pierrick — Thu, 25 Jan 2024 17:24:05 GMT

Hello @RVeitch_84,

if I understand correctly, you want to rotate your table to have each column in a row.
I suggest you take a look at the function Crosstable

Re: Looping through multiple columns Tmap

Vegar — Thu, 25 Jan 2024 21:19:21 GMT

Try something like this.Assuming you have 6 fields in the beginning of your table that you don't want to pivot.

new_table:

CROSSTABLE(meter_type, end_value,6)

Load * from [table];

Re: Looping through multiple columns Tmap

rwunderlich — Sun, 28 Jan 2024 17:10:43 GMT

I think this is a Talend question.

Re: Looping through multiple columns Tmap

RVeitch_84 — Wed, 31 Jan 2024 14:37:29 GMT

I solved it by using a unpivot in my input sql.